package com.hyt.it.ogt.ykcj.service.zs.impl;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.ListUtil;
import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.io.StreamProgress;
import cn.hutool.core.lang.Assert;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.text.StrPool;
import cn.hutool.core.thread.ThreadUtil;
import cn.hutool.core.util.*;
import cn.hutool.http.HttpUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.handler.context.SheetWriteHandlerContext;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.google.common.collect.ImmutableMap;
import com.google.common.collect.Iterators;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.hyt.check.CheckUtil;
import com.hyt.check.ruler.Ruler;
import com.hyt.check.ruler.summary.CollRuler;
import com.hyt.check.ruler.summary.IntRuler;
import com.hyt.check.ruler.summary.LongRuler;
import com.hyt.check.ruler.summary.StrRuler;
import com.hyt.common.apiInfor.ApiResponse;
import com.hyt.core.base.BaseEntity;
import com.hyt.core.base.BaseServiceImpl;
import com.hyt.core.constant.CommonConstant;
import com.hyt.core.exception.CheckException;
import com.hyt.core.util.ExcelUtils;
import com.hyt.core.util.FastJsonUtil;
import com.hyt.core.util.RegexUtils;
import com.hyt.core.util.UUIDUtils;
import com.hyt.exception.exception.BusinessLogicException;
import com.hyt.exception.exception.InvalidRequestException;
import com.hyt.it.ogt.common.config.GlobalConfig;
import com.hyt.it.ogt.common.config.RedisKeyConstant;
import com.hyt.it.ogt.ykcj.common.annotation.zs.TemplateMapper;
import com.hyt.it.ogt.ykcj.common.constant.Constants;
import com.hyt.it.ogt.ykcj.common.enums.CommonEnums;
import com.hyt.it.ogt.ykcj.common.enums.ExamFormEnums;
import com.hyt.it.ogt.ykcj.common.enums.ExamResultDownLoadStatusEnum;
import com.hyt.it.ogt.ykcj.common.enums.zs.*;
import com.hyt.it.ogt.ykcj.common.exception.CustomException;
import com.hyt.it.ogt.ykcj.common.utils.StringUtils;
import com.hyt.it.ogt.ykcj.common.utils.bean.BeanUtils;
import com.hyt.it.ogt.ykcj.common.utils.function.BigConsumer;
import com.hyt.it.ogt.ykcj.domain.exam.ExamResultDataDownLoadRecord;
import com.hyt.it.ogt.ykcj.domain.grade.EnterpriseExamineeCertificateRelation;
import com.hyt.it.ogt.ykcj.domain.grade.EnterpriseExamineeInfo;
import com.hyt.it.ogt.ykcj.domain.param.CertificatePageParam;
import com.hyt.it.ogt.ykcj.domain.param.EnterpriseScoreLineListParam;
import com.hyt.it.ogt.ykcj.domain.param.EnterpriseScoreLineSaveParam;
import com.hyt.it.ogt.ykcj.domain.vo.ExamInfoVo;
import com.hyt.it.ogt.ykcj.domain.zs.*;
import com.hyt.it.ogt.ykcj.domain.zs.dto.*;
import com.hyt.it.ogt.ykcj.domain.zs.vo.*;
import com.hyt.it.ogt.ykcj.mapper.exam.ExamInfoMapper;
import com.hyt.it.ogt.ykcj.mapper.grade.EnterpriseExamineeInfoMapper;
import com.hyt.it.ogt.ykcj.mapper.zs.ZsInfoMapper;
import com.hyt.it.ogt.ykcj.service.exam.IEnterpriseExamScoreLineSettingService;
import com.hyt.it.ogt.ykcj.service.exam.IExamInfoService;
import com.hyt.it.ogt.ykcj.service.exam.IExamResultDataDownLoadRecordService;
import com.hyt.it.ogt.ykcj.service.grade.IEnterpriseExamineeCertificateRelationService;
import com.hyt.it.ogt.ykcj.service.grade.IEnterpriseExamineeInfoService;
import com.hyt.it.ogt.ykcj.service.system.IFileUpHwCloudService;
import com.hyt.it.ogt.ykcj.service.zs.*;
import com.hyt.it.ogt.ykcj.utils.ExecutorPoolUtil;
import com.hyt.it.ogt.ykcj.utils.RedisLock;
import com.hyt.loginfo.service.IBaseLoginService;
import com.hyt.progress.constants.ProgressConstant;
import com.hyt.progress.service.ProgressManager;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.nio.charset.Charset;
import java.nio.charset.StandardCharsets;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.time.LocalDateTime;
import java.util.*;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.Future;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.TimeoutException;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.function.Function;
import java.util.stream.Collectors;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
import java.util.zip.ZipOutputStream;

/**
 * 证书信息接口
 *
 * @author admin
 * @date 2021-08-10
 */
@Slf4j
@Service
public class ZsInfoServiceImpl extends BaseServiceImpl<ZsInfoMapper, ZsInfo> implements IZsInfoService {

    @Resource
    GlobalConfig globalConfig;
    @Resource
    ThreadPoolTaskExecutor executor;
    @Resource
    IBaseLoginService loginService;
    @Resource
    private ZsInfoMapper zsInfoMapper;
    @Resource
    private ExamInfoMapper examInfoMapper;
    @Resource
    private IZsExamineeInfoService iZsExamineeInfoService;
    @Resource
    private EnterpriseExamineeInfoMapper enterpriseExamineeInfoMapper;
    @Resource
    private IEnterpriseExamScoreLineSettingService enterpriseExamScoreLineSettingService;
    @Resource
    IZsExamineeExtInfoService zsExamineeExtInfoService;
    @Resource
    IExamInfoService examInfoService;
    @Resource
    IZsTemplateService templateService;
    @Resource
    IZsTemplateConfigService templateConfigService;
    @Resource
    IFileUpHwCloudService fileUpHwCloudService;
    @Resource
    IZsExamineeExtInfoService examineeExtInfoService;
    @Resource
    private RedisLock redisLock;
    @Autowired
    private IExamResultDataDownLoadRecordService examResultDataDownLoadRecordService;
    @Resource
    IEnterpriseExamineeCertificateRelationService enterpriseExamineeCertificateRelationService;
    @Resource
    IEnterpriseExamineeInfoService enterpriseExamineeInfoService;

    @Override
    public void downloadExamineeImportTemplate(String infoId, HttpServletResponse response) throws IOException {
        ZsInfo zsInfo = Optional.ofNullable(this.getById(infoId))
                .orElseThrow(() -> new CustomException("不存在的证书信息"));

        String templateId = Optional.ofNullable(zsInfo.getCertificateTemplateid())
                .filter(StrUtil::isNotBlank)
                .orElseThrow(() -> new CustomException("当前证书未绑定模板"));

        List<ZsTemplateConfig> templateConfigs = templateConfigService.listImportConfigByTemplate(templateId);
        if (CollUtil.isEmpty(templateConfigs)) {
            throw new CustomException("当前证书绑定的模板还未进行规则配置");
        }

        Map<String, List<String>> specialityMap = templateService.listSpecialities(templateId)
                .stream()
                .map(data -> {
                    List<String> levelNames = templateService.listLevels(data.getSpecialityValue())
                            .stream()
                            .map(LevelDTO::getLevelName)
                            .distinct()
                            .collect(Collectors.toList());
                    return ImmutableMap.<String, List<String>>builder()
                            .put(data.getSpecialityName(), levelNames)
                            .build();
                }).map(ImmutableMap::entrySet)
                .flatMap(Collection::stream)
                .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue));

        // 导出证书人员导入模板
        exportCertificateTemplate(zsInfo.getCertificateName(), templateConfigs, specialityMap, response);
    }

    @Override
    public void downloadCertificateCodeTemplate(String infoId, HttpServletResponse response) throws IOException {
        ZsInfo zsInfo = Optional.ofNullable(this.getById(infoId))
                .orElseThrow(() -> new CustomException("不存在的证书信息"));

        List<ZsExamineeInfo> zsExamineeInfos = iZsExamineeInfoService.lambdaQuery()
                .eq(ZsExamineeInfo::getZsId, infoId)
                .eq(BaseEntity::getDelFlag, Boolean.FALSE)
                .list();
        List<CertificateCodeTemplateVO> codeTemplateVOS = zsExamineeInfos.stream()
                .map(data -> CertificateCodeTemplateVO.builder()
                        .name(data.getExamineeName())
                        .idNo(data.getIdentificationNo())
                        .certificateNo(data.getCertificateSerialNumber())
                        .issuedDate(data.getCertificateIssueData())
                        .build())
                .collect(Collectors.toList());

        String lastFileName = zsInfo.getCertificateName() + "证书编码.xlsx";
        response.setContentType("application/msexcel;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(lastFileName, "UTF-8"));
        EasyExcel.write(response.getOutputStream(), CertificateCodeTemplateVO.class)
                .registerWriteHandler(new SheetWriteHandler() {
                    @Override
                    public void afterSheetCreate(SheetWriteHandlerContext context) {
                        log.info("第{}个Sheet写入成功。", context.getWriteSheetHolder().getSheetNo());

                        // 检查的区域   new CellRangeAddressList(-1, -1, 3, 4);  表示第四行整列
                        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(CommonConstant.ONE, globalConfig.getXlsMaxRow(), 3, 3);
                        DataValidationHelper helper = context.getWriteSheetHolder().getSheet().getDataValidationHelper();
                        //DVConstraint constrain1 = DVConstraint.CreateDateConstraint(条件,"最小时间","最大时间","时间格式"); //这是检查时间的方法
                        DataValidationConstraint constraint = helper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN, "Date(1900, 1, 1)", "Date(2099, 12, 31)", "yyyy-MM-dd");
                        DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
                        // 输入无效值时是否显示错误框
                        dataValidation.setShowErrorBox(true);
                        // 验证输入数据是否真确
                        dataValidation.setSuppressDropDownArrow(true);
                        // 设置无效值时 是否弹出提示框
                        dataValidation.setShowPromptBox(true);
                        // 设置提示框内容 createPromptBox
                        // 设置无效值时的提示框内容 createErrorBox
                        dataValidation.createPromptBox("温馨提示", "请输入[yyyy/MM/dd]格式日期！！！");
                        context.getWriteSheetHolder().getSheet().addValidationData(dataValidation);

                    }
                })
                .sheet(zsInfo.getCertificateName() + "证书编码").doWrite(codeTemplateVOS);
    }

    @Override
    public void issueCertificate(String infoId, String consumerId) {
        String key = Constants.BATCH_LOCK_KEY_HEAD + infoId;
        try {
            ZsInfo zsInfo = Optional.ofNullable(this.getById(infoId))
                    .orElseThrow(() -> new CustomException("未查询到当前所属的证书信息"));
            ProgressManager.updateProgressCurrent(5L, "证书查询完成");

            ZsTemplate zsTemplate = Optional.ofNullable(templateService.getById(zsInfo.getCertificateTemplateid()))
                    .orElseThrow(() -> new CustomException("未查询到当前所属的证书模板"));
            ProgressManager.updateProgressCurrent(10L, "模板查询完成");

            // 获取模板发放的渲染配置
            List<ZsTemplateConfig> zsTemplateConfigs = templateConfigService.listTemplateRenderConfig(zsTemplate.getId());
            if (CollUtil.isEmpty(zsTemplateConfigs)) {
                throw new CustomException("未查询到当前所属的证书模板配置信息");
            }
            ProgressManager.updateProgressCurrent(15L, "模板发放渲染配置完成");

            Integer countSerialNumberIsNull = iZsExamineeInfoService.countSerialNumberIsNull(infoId);
            if (countSerialNumberIsNull > CommonConstant.ZERO) {
                throw new CustomException("存在部分没有证书编码的数据，不允许批量发放！");
            }
            ProgressManager.updateProgressCurrent(20L, "考生证书编码校验完成");

            String userId = loginService.getUserId();
            ProgressManager.getProgressTaskExecutor().submitListenable(() -> {
                // 批量创建证书
                batchCreateCertificate(zsInfo, zsTemplate, zsTemplateConfigs, userId, consumerId);
            }).addCallback(data -> {
                // 获取证书发放锁
                redisLock.unLock(key, consumerId);
                ProgressManager.finish(Boolean.TRUE);
            }, ex -> {
                // 获取证书发放锁
                redisLock.unLock(key, consumerId);
                ProgressManager.updateProgressFailMessage(52402516, "全部发放证书业务执行失败");
            });
        } catch (Exception e) {
            // 获取证书发放锁
            redisLock.unLock(key, consumerId);
            throw e;
        }
    }

    @Override
    @SuppressWarnings("unchecked")
    public ImportCertificateExamineesMessageVO importCertificateExamineesCheck(MultipartFile file, String infoId) throws IOException {
        ZsInfo zsInfo = Optional.ofNullable(this.getById(infoId))
                .orElseThrow(() -> new CustomException("不存在的证书信息"));

        List<ZsTemplateConfig> templateConfigs = listTemplateConfigByTemplateId(zsInfo.getCertificateTemplateid());
        Map<String, ZsTemplateConfig> codeMapTemplateConfigs = templateConfigs.stream()
                .collect(Collectors.toMap(ZsTemplateConfig::getVariableCode, Function.identity()));

        return assembleCertificateExamineesCheckInfo(file, codeMapTemplateConfigs, zsInfo, assembleSubjectCheckHandle(zsInfo, codeMapTemplateConfigs));
    }

    /**
     * 装配证书专业、级别的校验
     *
     * @return
     */
    @SuppressWarnings("unchecked")
    private BigConsumer<String, Map<String, String>, CheckUtil.CheckHandler> assembleSubjectCheckHandle(ZsInfo zsInfo, Map<String, ZsTemplateConfig> codeMapTemplateConfigs) {
        return (infoId, codeMappingValue, checkHandler) -> {
            ZsTemplateConfig occupationTemplateConfig = codeMapTemplateConfigs.get(VariableCodeEnums.OCCUPATION.name());
            if (occupationTemplateConfig != null) {
                String importSubjectName = codeMappingValue.get(VariableCodeEnums.OCCUPATION.getCode());
                // 获取专业名称
                List<SpecialityDTO> specialityDTOS = templateService.listSpecialities(zsInfo.getCertificateTemplateid());
                String specialityValue = Iterators.tryFind(specialityDTOS.iterator(),
                                data -> StrUtil.equals(data.getSpecialityName(), importSubjectName))
                        .toJavaUtil()
                        .map(SpecialityDTO::getSpecialityValue)
                        .orElseGet(String::new);
                checkHandler.collect(specialityValue, "模板中不存在的证书专业", Boolean.FALSE, StrRuler.notBlank());

                ZsTemplateConfig levelTemplateConfig = codeMapTemplateConfigs.get(VariableCodeEnums.LEVEL.name());
                if (levelTemplateConfig != null) {
                    String importLevelName = codeMappingValue.get(VariableCodeEnums.LEVEL.getCode());
                    // 获取专业级别
                    List<LevelDTO> levelDTOS = templateService.listLevels(specialityValue);
                    String levelValue = Iterators.tryFind(levelDTOS.iterator(),
                                    data -> StrUtil.equals(data.getLevelName(), importLevelName))
                            .toJavaUtil()
                            .map(LevelDTO::getLevelValue)
                            .orElseGet(String::new);
                    checkHandler.collect(levelValue, "模板中不存在的证书级别", Boolean.FALSE, StrRuler.notBlank());
                }
            }
        };
    }

    /**
     * 装配考生的证书检查信息
     *
     * @param file
     * @param templateConfigs
     * @param zsInfo
     * @return
     * @throws IOException
     */
    @SuppressWarnings("all")
    @SneakyThrows
    private ImportCertificateExamineesMessageVO assembleCertificateExamineesCheckInfo(MultipartFile file,
                                                                                      Map<String, ZsTemplateConfig> codeMapTemplateConfigs,
                                                                                      ZsInfo zsInfo,
                                                                                      BigConsumer<String, Map<String, String>, CheckUtil.CheckHandler>... checkAfterHandlers) {
        // 获取excel中的数据List<Map<Code, Value>>
        List<Map<String, String>> codeMappingValue = getExcelDataList(file.getInputStream(), codeMapTemplateConfigs);
        if (CollUtil.isEmpty(codeMappingValue)) {
            throw new CustomException("当前导入的模板数据为空");
        }

        // 校验证书考生信息
        List<ImportCertificateExamineesVO> importCertificateExamineesVOS = this.checkCertificateExamineesData(zsInfo, codeMapTemplateConfigs, codeMappingValue, checkAfterHandlers);
        // Map<ValidStatus, List<ImportCertificateExamineesVO>>
        Map<Boolean, List<ImportCertificateExamineesVO>> validResult = importCertificateExamineesVOS.stream()
                .collect(Collectors.groupingBy(ImportCertificateExamineesVO::getValidStatus));

        // 验证通过的数据List<Map<Code, Value>>
        List<Map<String, String>> successCertificateExaminees = Optional.ofNullable(validResult)
                .map(data -> data.get(Boolean.FALSE))
                .orElseGet(Lists::newArrayList)
                .stream()
                .map(ImportCertificateExamineesVO::getCertificateExaminees)
                .collect(Collectors.toList());

        // 检查证件号码是否在库中存在，这里使用校验通过的考生数据，因为校验没通过的数据不需要做这一步操作
        Boolean hasRepeat = checkIdNoRepeat(successCertificateExaminees, zsInfo.getId());

        return ImportCertificateExamineesMessageVO.builder()
                .certificateExaminees(successCertificateExaminees)
                .validSuccessResult(validResult.get(Boolean.FALSE))
                .validFailResult(validResult.get(Boolean.TRUE))
                .hasRepeat(hasRepeat)
                .build();
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    @SuppressWarnings("unchecked")
    public Boolean importCertificateExamineesData(List<Map<String, String>> certificateExaminees, String infoId) {
        ZsInfo zsInfo = Optional.ofNullable(this.getById(infoId))
                .orElseThrow(() -> new CustomException("不存在的证书信息"));

        // 通过模板ID获取模板的配置信息
        List<ZsTemplateConfig> templateConfigs = listTemplateConfigByTemplateId(zsInfo.getCertificateTemplateid());
        Map<String, ZsTemplateConfig> codeMapTemplateConfigs = templateConfigs.stream()
                .collect(Collectors.toMap(ZsTemplateConfig::getVariableCode, Function.identity()));        
        
        // 校验证书考生信息
        List<ImportCertificateExamineesVO> importCertificateExamineesVOS = checkCertificateExamineesData(zsInfo, codeMapTemplateConfigs, certificateExaminees, assembleSubjectCheckHandle(zsInfo, codeMapTemplateConfigs));
        // 校验并抛出证书校验异常数据
        throwCertificateExamineesDataError(importCertificateExamineesVOS);
        log.info("# 导入证书人员数据检查完成：{}", infoId);

        for (Map<String, String> certificateExaminee : certificateExaminees) {
            log.debug("# 导入证书人员数据，开始操作{}的数据", certificateExaminee.get(VariableCodeEnums.NAME.getCode()));
            // 装配考生信息
            ZsExamineeInfo examineesInfoTemp = assembleExamineesInfo(certificateExaminee, zsInfo);
            // 获取进行数据库操作的对象
            ZsExamineeInfo examineeInfoUpdate = iZsExamineeInfoService.listByIdNo(examineesInfoTemp.getIdentificationNo(), infoId)
                    .stream()
                    .map(data -> {
                        ZsExamineeInfo tempExaminees = BeanUtil.toBean(examineesInfoTemp, ZsExamineeInfo.class);
                        tempExaminees.setId(data.getId())
                                .setUpdateBy(loginService.getUserId());
                        return tempExaminees;
                    })
                    .findFirst()
                    .map(data -> {
                        examineeExtInfoService.lambdaUpdate()
                                .eq(ZsExamineeExtInfo::getExamineeId, data.getId())
                                .set(BaseEntity::getUpdateBy, loginService.getUserId())
                                .set(BaseEntity::getUpdateDate, LocalDateTime.now())
                                .set(BaseEntity::getDelFlag, Boolean.TRUE)
                                .update();
                        return data;
                    })
                    .orElse(examineesInfoTemp);
            iZsExamineeInfoService.saveOrUpdate(examineeInfoUpdate);
            // 装配考生拓展信息
            List<ZsExamineeExtInfo> examineesExtInfos = assembleExamineesInfoExts(examineeInfoUpdate.getId(), certificateExaminee, templateConfigs);
            examineeExtInfoService.saveBatch(examineesExtInfos);
            // 处理考生证书关系绑定逻辑
            this.handleEnterpriseExamineeCertificateRelation(zsInfo, examineeInfoUpdate);
        }

        return Boolean.TRUE;
    }

    /**
     * 处理考生证书关系绑定逻辑
     *
     * @param zsInfo
     * @param examineesInfo
     */
    private void handleEnterpriseExamineeCertificateRelation(ZsInfo zsInfo, ZsExamineeInfo examineesInfo) {
        if (!NumberUtil.equals(zsInfo.getCertificatePublishType(), CertificatePublishTypeEnums.RELATION_CJ_PUBLISH.getCode())) {
            log.trace("#当前证书为{}模式，不需要同步关联成绩数据", CertificatePublishTypeEnums.SINGLE_PUBLISH.getDesc());
            return;
        }

        List<EnterpriseExamineeInfo> enterpriseExamineeInfos = enterpriseExamineeInfoService.getExamineeInfoByIdentificationNo(zsInfo.getExamId(), examineesInfo.getIdentificationNo());
        if (CollUtil.isEmpty(enterpriseExamineeInfos)) {
            log.debug("#处理考生证书关系绑定没有查询到成绩中的该考生{}", examineesInfo.getIdentificationNo());
            return;
        }

        List<EnterpriseExamineeCertificateRelation> examineeCertificateRelationSaveList = enterpriseExamineeInfos.stream()
                .map(enterpriseExamineeInfo -> {
                    // 企业考生证书关联
                    EnterpriseExamineeCertificateRelation examineeCertificateRelation = EnterpriseExamineeCertificateRelation
                            .builder()
                            .id(UUIDUtils.newSortUUID())
                            .enterpriseExamineeInfoId(enterpriseExamineeInfo.getId())
                            .examInfoId(enterpriseExamineeInfo.getExamId())
                            .zsInfoId(zsInfo.getId())
                            .zsExamineeInfoId(examineesInfo.getId())
                            .build();
                    return examineeCertificateRelation;
                }).collect(Collectors.toList());

        enterpriseExamineeCertificateRelationService.saveBatch(examineeCertificateRelationSaveList);
    }

    /**
     * 校验并抛出证书校验异常数据
     *
     * @param importCertificateExamineesVOS
     */
    private void throwCertificateExamineesDataError(List<ImportCertificateExamineesVO> importCertificateExamineesVOS) {
        Assert.notEmpty(importCertificateExamineesVOS, "证书校验数据不存在");
        importCertificateExamineesVOS.stream()
                .filter(ImportCertificateExamineesVO::getValidStatus)
                .filter(data -> CollUtil.isNotEmpty(data.getValidMessage()))
                .forEach(data -> {
                    throw new CustomException(CollUtil.join(data.getValidMessage(), StrUtil.COMMA));
                });
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public void importCertificateExamineesPhoto(String zipFileUrl, String infoId) throws IOException {
        String fileSuffix = FileUtil.getSuffix(zipFileUrl);
        if (!StrUtil.equals(fileSuffix, ZIP_SUFFIX)) {
            BusinessLogicException.throwException(43330403, "上传的文件类型不匹配");
        }
        File uploadFile = FileUtil.createTempFile(StrPool.DOT + ZIP_SUFFIX, Boolean.TRUE);
        long fileSize = HttpUtil.downloadFile(zipFileUrl, uploadFile);
        if (fileSize == CommonConstant.ZERO) {
            BusinessLogicException.throwException(43330402, "请勿上传一个空文件");
        }
        if (fileSize > globalConfig.getHeadPhotoFileMaxSizeToByte()) {
            BusinessLogicException.throwException(ProgressConstant.RESPONSE_CODE_BUSINESS_ERROR, StrUtil.format("上传的文件大小不得超过{}MB", globalConfig.getHeadPhotoFileMaxSize()));
        }
        ProgressManager.updateProgressCurrent(5L, "文件接收成功");

        List<CertificateExamineesPhotoFileDTO> examineesPhotoFileDTOS = getZipFiles(uploadFile);
        if (CollUtil.isEmpty(examineesPhotoFileDTOS)) {
            throw new BusinessLogicException(51150308, "未解析出证件");
        }
        ProgressManager.updateProgressCurrent(10L, "考生证件照解析完毕");

        Map<String, String> idNoMappingPhotoUrl = checkCertificateExamineesPhotoData(examineesPhotoFileDTOS);
        ProgressManager.updateProgressCurrent(80L, "校验考生证件照文件完毕");

        Set<Map.Entry<String, String>> entries = idNoMappingPhotoUrl.entrySet();
        for (int i = 0; i < entries.size(); i++) {
            Map.Entry<String, String> entry = CollUtil.get(entries, i);
            String idNo = entry.getKey();
            String photoUrl = entry.getValue();

            Optional<ZsExamineeInfo> zsExamineesInfoOpt = iZsExamineeInfoService.lambdaQuery()
                    .eq(ZsExamineeInfo::getZsId, infoId)
                    .eq(ZsExamineeInfo::getIdentificationNo, idNo)
                    .eq(BaseEntity::getDelFlag, Boolean.FALSE)
                    .oneOpt();
            if (!zsExamineesInfoOpt.isPresent()) {
                throw new BusinessLogicException(ProgressConstant.RESPONSE_CODE_BUSINESS_ERROR, StrUtil.format("证件号码为：{}的考生未匹配成功！", idNo));
            }
            ZsExamineeInfo examineesInfo = zsExamineesInfoOpt.get();
            iZsExamineeInfoService.lambdaUpdate()
                    .set(ZsExamineeInfo::getExamineePhoto, photoUrl)
                    .set(BaseEntity::getUpdateBy, loginService.getUserId())
                    .set(BaseEntity::getUpdateDate, LocalDateTime.now())
                    .eq(BaseEntity::getId, examineesInfo.getId())
                    .update();
            ProgressManager.stepProgressCurrent(i, entries.size(), 20L, 99L, StrUtil.format("证件号码为{}的考生证件照进行入库", idNo));
        }

        ProgressManager.updateProgressCurrent(100L, "考生证件照上传成功");
    }

    @Override
    @SuppressWarnings("unchecked")
    public ImportCertificateExamineesMessageVO importCertificateSerialNumberCheck(MultipartFile file, String infoId) {
        ZsInfo zsInfo = Optional.ofNullable(this.getById(infoId))
                .orElseThrow(() -> new CustomException("不存在的证书信息"));

        // 获取导入证书编码数据所需的模板配置
        List<ZsTemplateConfig> templateConfigs = getSerialNumberTemplateConfigs(zsInfo.getCertificateTemplateid());
        Map<String, ZsTemplateConfig> codeMapTemplateConfigs = templateConfigs.stream()
                .collect(Collectors.toMap(ZsTemplateConfig::getVariableCode, Function.identity()));

        ImportCertificateExamineesMessageVO importCertificateExamineesMessageVO = assembleCertificateExamineesCheckInfo(file, codeMapTemplateConfigs, zsInfo, assembleIdNoCheckHandlers());
        Optional.ofNullable(importCertificateExamineesMessageVO)
                .map(ImportCertificateExamineesMessageVO::getValidFailResult)
                .orElseGet(Lists::newArrayList)
                .stream()
                .filter(data -> CollUtil.isNotEmpty(data.getErrorCodes()))
                .forEach(data -> {
                    ImportDataVerifyStatus importDataVerifyStatus = ImportDataVerifyStatus.getByCodes(data.getErrorCodes());
                    switch (importDataVerifyStatus) {
                        case CERTIFICATE_ALREADY_ISSUE:
                            throw new CustomException(StrUtil.format(importDataVerifyStatus.getDesc(), data.getExamineesName()));
                        case CERTIFICATE_NO_ALREADY_ISSUE:
                            throw new CustomException(StrUtil.format(importDataVerifyStatus.getDesc(), data.getCertificateSerialNumber()));
                        default:
                            break;
                    }
                });
        return importCertificateExamineesMessageVO;
    }

    /**
     * 获取导入证书编码数据所需的模板配置
     *
     * @param templateId
     * @return
     */
    private List<ZsTemplateConfig> getSerialNumberTemplateConfigs(String templateId) {
        List<ZsTemplateConfig> templateConfigs = listTemplateConfigByTemplateId(templateId);

        return templateConfigs.stream()
                .filter(data -> StrUtil.equals(data.getVariableCode(), VariableCodeEnums.ID_NO.getCode()) ||
                        StrUtil.equals(data.getVariableCode(), VariableCodeEnums.CERTIFICATE_NO.getCode()) ||
                        StrUtil.equals(data.getVariableCode(), VariableCodeEnums.NAME.getCode()) ||
                        StrUtil.equals(data.getVariableCode(), VariableCodeEnums.ISSUE_DATE.getCode()))
                .collect(Collectors.toList());
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public Long importCertificateSerialNumberData(List<Map<String, String>> certificateSerialNumbers, String infoId) {
        ZsInfo zsInfo = Optional.ofNullable(this.getById(infoId))
                .orElseThrow(() -> new CustomException("不存在的证书信息"));

        // 获取导入证书编码数据所需的模板配置
        List<ZsTemplateConfig> templateConfigs = getSerialNumberTemplateConfigs(zsInfo.getCertificateTemplateid());
        Map<String, ZsTemplateConfig> codeMapTemplateConfigs = templateConfigs.stream()
                .collect(Collectors.toMap(ZsTemplateConfig::getVariableCode, Function.identity()));

        // 检查导入证书编码数据
        List<ImportCertificateExamineesVO> validErrorData = checkCertificateSerialNumberData(zsInfo, codeMapTemplateConfigs, certificateSerialNumbers)
                .stream()
                .filter(ImportCertificateExamineesVO::getValidStatus)
                .collect(Collectors.toList());
        log.debug("# 导入证书编码数据验证不通过：{}", FastJsonUtil.getBeanToJson(validErrorData));
        CheckUtil.check(validErrorData, "导入证书编码数据验证不通过", Boolean.FALSE, CollRuler.isEmpty());
        log.info("# 导入证书编码数据检查完成：{}", infoId);

        // 装配证书编码模板
        List<CertificateCodeTemplateVO> certificateCodes = assembleCertificateCodeTemplates(certificateSerialNumbers);
        log.debug("# 导入证书编码装配证书编码模板：{}", FastJsonUtil.getBeanToJson(certificateCodes));

        return certificateCodes.stream()
                .map(certificateCode -> {
                    Date validityEnd = CertificateValidityType.offsetDate(certificateCode.getIssuedDate(), zsInfo.getCertificateValidityType());

                    boolean certificateCodeUpdateStatus = iZsExamineeInfoService.lambdaUpdate()
                            .set(ZsExamineeInfo::getCertificateSerialNumber, certificateCode.getCertificateNo())
                            .set(ZsExamineeInfo::getCertificateIssueData, certificateCode.getIssuedDate())
                            .set(ZsExamineeInfo::getCertificateValidityStart, certificateCode.getIssuedDate())
                            .set(ZsExamineeInfo::getCertificateValidityEnd, validityEnd)
                            .set(BaseEntity::getUpdateBy, loginService.getUserId())
                            .set(BaseEntity::getUpdateDate, LocalDateTime.now())
                            .eq(ZsExamineeInfo::getZsId, zsInfo.getId())
                            .eq(ZsExamineeInfo::getIdentificationNo, certificateCode.getIdNo())
                            .eq(BaseEntity::getDelFlag, Boolean.FALSE)
                            .update();
                    log.debug("# 导入证书编码数据更新完成，{}更新状态为{}", certificateCode.getIdNo(), certificateCodeUpdateStatus);
                    return certificateCodeUpdateStatus;
                })
                .filter(data -> data)
                .count();
    }

    @Override
    public String batchDownloadCertificate(String infoId, ExamResultDataDownLoadRecord finalProjectDataDownLoadRecord, HttpServletResponse response) throws Exception {
        ZsInfo zsInfo = Optional.ofNullable(this.getById(infoId))
                .orElseThrow(() -> new CustomException("不存在的证书信息"));
        List<ZsExamineeInfo> examineesInfos = iZsExamineeInfoService.lambdaQuery()
                .eq(ZsExamineeInfo::getZsId, infoId)
                .eq(ZsExamineeInfo::getCertificateProvideStatus, CertificateProvideStatusEnums.PROVIDED.getCode())
                .eq(BaseEntity::getDelFlag, Boolean.FALSE)
                .list();
        log.debug("批量下载证书生成数据：{}", FastJsonUtil.getBeanToJson(examineesInfos));

        String lastFileName = zsInfo.getCertificateName() + ".zip";
        Future<String> fileFuture = ExecutorPoolUtil.getPoolTaskExecutor().submit(() -> {
            String fileUrl = exportCertificateHasSignaturePdfZip(lastFileName, examineesInfos);
            // 更新文件下载记录
            if (StringUtils.isEmpty(fileUrl)) {
                // 更新文件下载记录
                finalProjectDataDownLoadRecord.setStatus(ExamResultDownLoadStatusEnum.fail.getStatus());
            } else {
                // 更新文件下载记录
                finalProjectDataDownLoadRecord.setStatus(ExamResultDownLoadStatusEnum.finish.getStatus());
            }
            finalProjectDataDownLoadRecord.setUpdateDate(LocalDateTime.now());
            finalProjectDataDownLoadRecord.setFileUrl(fileUrl);
            examResultDataDownLoadRecordService.updateById(finalProjectDataDownLoadRecord);
            return fileUrl;
        });
        String fileUrl = null;
        try {
            fileUrl = ExecutorPoolUtil.getPoolTaskExecutor().submit(() -> {
                while (!fileFuture.isDone()) {
                    ThreadUtil.sleep(100);
                }
                return fileFuture.get();
            }).get(globalConfig.getFileTimeOut(), TimeUnit.SECONDS);
        } catch (TimeoutException e) {
            log.error("# 22、{}文件生成超过指定等待时间，提前返回", lastFileName);
        }
        return fileUrl;
    }

    @SneakyThrows
    @Override
    public void exportCertificateExamineesExcel(String infoId, HttpServletResponse response) {
        ZsInfo zsInfo = Optional.ofNullable(this.getById(infoId))
                .orElseThrow(() -> new CustomException("不存在的证书信息"));
        List<ExportExamineesInfoDTO> examineesInfos = iZsExamineeInfoService.lambdaQuery()
                .eq(ZsExamineeInfo::getZsId, infoId)
                .eq(BaseEntity::getDelFlag, Boolean.FALSE)
                .list()
                .stream()
                .map(data -> {
                    ExportExamineesInfoDTO exportExamineesInfoDTO = BeanUtil.toBean(data, ExportExamineesInfoDTO.class);
                    exportExamineesInfoDTO.setExamineeSexDesc(SexEnums.getDescByType(data.getExamineeSex()))
                            .setIdentificationTypeDesc(IdentityTypeEnum.getDescByType(data.getIdentificationType()))
                            .setCertificateProvideStatusDesc(CertificateProvideStatusEnums.getDescByCode(data.getCertificateProvideStatus()));

                    String certificateValidityStart = DateUtil.format(data.getCertificateValidityStart(), DatePattern.NORM_DATE_PATTERN);
                    String certificateValidityEnd = DateUtil.format(data.getCertificateValidityEnd(), DatePattern.NORM_DATE_PATTERN);
                    String mergeValidityDate = StrUtil.format("{}至{}",
                            Optional.ofNullable(certificateValidityStart)
                                    .orElse(StrPool.DASHED),
                            Optional.ofNullable(certificateValidityEnd)
                                    .orElse(StrPool.DASHED));
                    exportExamineesInfoDTO.setCertificateValidityDateDesc(mergeValidityDate);

                    return exportExamineesInfoDTO;
                })
                .collect(Collectors.toList());

        String lastFileName = zsInfo.getCertificateName() + ".xlsx";
        response.setContentType("application/msexcel;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(lastFileName, "UTF-8"));
        EasyExcel.write(response.getOutputStream(), ExportExamineesInfoDTO.class)
                .sheet(zsInfo.getCertificateName()).doWrite(examineesInfos);
    }

    /**
     * 导出带有电子签章的ZIP文件
     *
     * @param fileName
     * @param examineesInfos
     * @return
     */
    @Override
    public String exportCertificateHasSignaturePdfZip(String fileName, List<ZsExamineeInfo> examineesInfos) {
        List<ZsExamineeInfoExportZip> examineeInfoExportZips = examineesInfos.stream()
                .map(data -> {
                    ZsExamineeInfoExportZip examineeInfoExportZip = BeanUtil.toBean(data, ZsExamineeInfoExportZip.class);
                    examineeInfoExportZip.setUrl(data.getCertificatePdfUrl());
                    return examineeInfoExportZip;
                })
                .collect(Collectors.toList());
        return exportCertificatePdfZip(fileName, examineeInfoExportZips);
    }

    @Override
    public List<ZsExamInfoVO> queryExamByOfficeId(Set<String> deptIds) {
        Map<String, Object> parms = new HashMap<>();
        parms.put("deptIds", deptIds);
        parms.put("examForm", ExamFormEnums.FORM_EXAM.getCode());
        List<ExamInfoVo> list = examInfoMapper.selectExamInfoByParms(parms);

        List<ZsExamInfoVO> voList = list.stream()
                .map(examInfoVo -> {
                    ZsExamInfoVO vo = new ZsExamInfoVO();
                    vo.setExamId(examInfoVo.getId());
                    vo.setOfficeId(examInfoVo.getOfficeId());
                    vo.setDeptId(examInfoVo.getDeptId());
                    vo.setExamName(examInfoVo.getExamNameConfigName());
                    return vo;
                }).collect(Collectors.toList());
        return voList;
    }

    @Override
    public ZsInfoVO getZsInfoByDataSourceToExam(String examId) {
        QueryWrapper<ZsInfo> queryWrapper = new QueryWrapper<>();
        queryWrapper.lambda()
                .eq(ZsInfo::getExamId, examId)
                .eq(ZsInfo::getDelFlag, false)
                .eq(ZsInfo::getCertificateDataSource, CertificateDataSourceEnums.RELATE_CJ.getCode())
                // 这里要升序排序是只拿取了一条数据的原因
                .orderByAsc(ZsInfo::getCertificateStatus)
                .last("limit 1");

        ZsInfo zsInfo = zsInfoMapper.selectOne(queryWrapper);
        if (null == zsInfo) {
            return null;
        }
        ZsInfoVO vo = BeanUtil.toBean(zsInfo, ZsInfoVO.class);
        return vo;
    }

    @Override
    public List<ZsInfoVO> listZsInfoByDataSourceToExam(String examId) {
        QueryWrapper<ZsInfo> queryWrapper = new QueryWrapper<>();
        queryWrapper.lambda()
                .eq(ZsInfo::getExamId, examId)
                .eq(ZsInfo::getDelFlag, Boolean.FALSE)
                .eq(ZsInfo::getCertificateDataSource, CertificateDataSourceEnums.RELATE_CJ.getCode())
                // 这里要升序排序是只拿取了一条数据的原因
                .orderByAsc(ZsInfo::getCertificateStatus);
        List<ZsInfo> zsInfos = zsInfoMapper.selectList(queryWrapper);

        List<ZsInfoVO> collect = zsInfos.stream()
                .map(data -> {
                    ZsInfoVO vo = BeanUtil.toBean(data, ZsInfoVO.class);
                    return vo;
                }).collect(Collectors.toList());
        return collect;
    }

    @Override
    public ZsInfoVO getZsInfoByPublishTypeToExam(String examId) {
        QueryWrapper<ZsInfo> queryWrapper = new QueryWrapper<>();
        queryWrapper.lambda()
                .eq(ZsInfo::getExamId, examId)
                .eq(ZsInfo::getDelFlag, false)
                .eq(ZsInfo::getCertificatePublishType, CertificatePublishTypeEnums.RELATION_CJ_PUBLISH.getCode())
                // 这里要升序排序是只拿取了一条数据的原因
                .orderByAsc(ZsInfo::getCertificateStatus)
                .last("limit 1");

        ZsInfo zsInfo = zsInfoMapper.selectOne(queryWrapper);
        if (null == zsInfo) {
            return null;
        }
        ZsInfoVO vo = BeanUtil.toBean(zsInfo, ZsInfoVO.class);
        return vo;
    }

    /**
     * 导出不带有电子签章的ZIP文件
     *
     * @param fileName
     * @param examineesInfos
     * @return
     * @throws IOException
     */
    private String exportCertificateNoSignaturePdfZip(String fileName, List<ZsExamineeInfo> examineesInfos) {
        List<ZsExamineeInfoExportZip> examineeInfoExportZips = examineesInfos.stream()
                .map(data -> {
                    ZsExamineeInfoExportZip examineeInfoExportZip = BeanUtil.toBean(data, ZsExamineeInfoExportZip.class);
                    examineeInfoExportZip.setUrl(data.getNoSignaturePdfUrl());
                    return examineeInfoExportZip;
                })
                .collect(Collectors.toList());
        return exportCertificatePdfZip(fileName, examineeInfoExportZips);
    }

    /**
     * 导出PDF证书到ZIP文件
     *
     * @param fileName
     * @param examineeInfoExportZips
     * @return
     * @throws IOException
     */
    @SneakyThrows
    @Override
    public String exportCertificatePdfZip(String fileName, List<ZsExamineeInfoExportZip> examineeInfoExportZips) {
        File tempZipFile = FileUtil.createTempFile(".zip", Boolean.TRUE);
        BufferedOutputStream bufferedOutputStream = FileUtil.getOutputStream(tempZipFile);
        ZipOutputStream zipOutputStream = new ZipOutputStream(bufferedOutputStream, StandardCharsets.UTF_8);
        for (ZsExamineeInfoExportZip examineeInfoExportZip : examineeInfoExportZips) {
            ZipEntry pdfEntry = new ZipEntry(examineeInfoExportZip.getExamineeName() + StrPool.UNDERLINE + examineeInfoExportZip.getIdentificationNo() + ".pdf");
            zipOutputStream.putNextEntry(pdfEntry);
            String certificatePdfUrl = examineeInfoExportZip.getUrl();
            HttpUtil.download(certificatePdfUrl, zipOutputStream, Boolean.FALSE, new StreamProgress() {
                @Override
                public void start() {
                    log.debug("批量下载证书开始下载pdf文件：{}", certificatePdfUrl);
                }

                @Override
                public void progress(long total, long progressSize) {
                    NumberFormat numberFormat = new DecimalFormat("#,###");
                    log.debug("批量下载证书下载pdf文件进行中：{}，文件总大小：{}，已经进行的大小：{}", certificatePdfUrl, numberFormat.format(total), numberFormat.format(progressSize));
                }

                @Override
                public void finish() {
                    log.debug("批量下载证书完成下载pdf文件：{}", certificatePdfUrl);
                }
            });
        }
        // 节点流来自servlet，处理流不进行关闭
        IoUtil.flush(zipOutputStream);
        IoUtil.close(zipOutputStream);
        log.debug("批量下载证书生成zip完成：{}", fileName);
        return fileUpHwCloudService.uploadFileToHuawei(tempZipFile, "zip", fileName);
    }

    /**
     * 装配证书编码模板
     *
     * @param certificateSerialNumbers
     * @return
     */
    private List<CertificateCodeTemplateVO> assembleCertificateCodeTemplates(List<Map<String, String>> certificateSerialNumbers) {
        return certificateSerialNumbers.stream()
                .map(this::assembleCertificateCodeTemplate)
                .collect(Collectors.toList());
    }

    /**
     * 装配证书编码模板
     *
     * @param codeMappingValue
     * @return
     */
    private CertificateCodeTemplateVO assembleCertificateCodeTemplate(Map<String, String> codeMappingValue) {
        CertificateCodeTemplateVO certificateCodeTemplateVO = new CertificateCodeTemplateVO();
        Field[] fields = ReflectUtil.getFields(certificateCodeTemplateVO.getClass(),
                field -> Optional.ofNullable(field.getAnnotation(TemplateMapper.class)).isPresent());
        for (Field field : fields) {
            Optional.ofNullable(field.getAnnotation(TemplateMapper.class))
                    .map(TemplateMapper::templateCode)
                    .filter(ArrayUtil::isNotEmpty)
                    .map(Arrays::asList)
                    .orElseGet(Lists::newArrayList)
                    .forEach(templateCode -> {
                        ReflectUtil.setFieldValue(certificateCodeTemplateVO, field, codeMappingValue.get(templateCode));
                    });
        }
        return certificateCodeTemplateVO;
    }


    /**
     * 校验导入证书编码数据
     *
     * @param zsInfo
     * @param certificateSerialNumbers
     * @return
     */
    @SuppressWarnings("unchecked")
    private List<ImportCertificateExamineesVO> checkCertificateSerialNumberData(ZsInfo zsInfo, Map<String, ZsTemplateConfig> codeMapTemplateConfigs, List<Map<String, String>> certificateSerialNumbers) {
        // 校验证书考生信息
        return checkCertificateExamineesData(zsInfo, codeMapTemplateConfigs, certificateSerialNumbers, assembleIdNoCheckHandlers());
    }

    @SuppressWarnings("unchecked")
    private BigConsumer<String, Map<String, String>, CheckUtil.CheckHandler> assembleIdNoCheckHandlers() {
        return (infoId, codeMappingValue, checkHandler) -> {
            String idNo = codeMappingValue.get(VariableCodeEnums.ID_NO.getCode());
            List<ZsExamineeInfo> zsExamineeInfos = iZsExamineeInfoService.lambdaQuery()
                    .eq(ZsExamineeInfo::getZsId, infoId)
                    .eq(ZsExamineeInfo::getIdentificationNo, idNo)
                    .eq(BaseEntity::getDelFlag, Boolean.FALSE)
                    .list();
            checkHandler.collect(zsExamineeInfos, "库中不存在考生信息", Boolean.FALSE, CollRuler.notEmpty());
        };
    }

    /**
     * 校验证件文件
     *
     * @param examineesPhotoFileDTOS
     * @return
     */
    private Map<String, String> checkCertificateExamineesPhotoData(List<CertificateExamineesPhotoFileDTO> examineesPhotoFileDTOS) {
        Map<String, Long> idNoMappingCount = examineesPhotoFileDTOS.stream()
                .peek(data -> {
                    String fileType = FileUtil.getSuffix(data.getPhotoFile());
                    if (!StrUtil.equals(fileType, FileUtil.getSuffix(IMAGE_SUFFIX))) {
                        throw new BusinessLogicException(ProgressConstant.RESPONSE_CODE_BUSINESS_ERROR, StrUtil.format("证件号码为{}的考生照片格式不匹配，请上传jpg格式照片！", data.getIdNo()));
                    }
                })
                .map(CertificateExamineesPhotoFileDTO::getIdNo)
                .map(this::checkIdNoValidity)
                .collect(Collectors.groupingBy(idNo -> idNo, Collectors.counting()));
        idNoMappingCount.forEach((idNo, count) -> {
            if (count > CommonConstant.ONE) {
                throw new BusinessLogicException(ProgressConstant.RESPONSE_CODE_BUSINESS_ERROR, StrUtil.format("证件号码为{}的考生照片存在多个，请调整！", idNo));
            }
        });
        ProgressManager.updateProgressCurrent(20L, "考生证件号码解析验证完毕");

        AtomicInteger atomicInteger = new AtomicInteger();
        return examineesPhotoFileDTOS.stream()
                .map(examineesPhotoFileDTO -> {
                    String photoUrl = this.uploadExamineePhotoFile(examineesPhotoFileDTO);
                    log.debug("# 导入考生{}照片URL：{}", examineesPhotoFileDTO.getIdNo(), photoUrl);

                    ProgressManager.stepProgressCurrent(atomicInteger.getAndIncrement(), examineesPhotoFileDTOS.size(), 50L, 80L, StrUtil.format("正在上传{}考生照片", examineesPhotoFileDTO.getIdNo()));

                    return ImmutableMap.<String, String>builder()
                            .put(checkIdNoValidity(examineesPhotoFileDTO.getIdNo()), photoUrl)
                            .build();
                })
                .map(Map::entrySet)
                .flatMap(Collection::stream)
                .collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue));
    }

    /**
     * 上传考生图片文件到华为云
     *
     * @param examineesPhotoFileDTO
     * @return
     */
    private String uploadExamineePhotoFile(CertificateExamineesPhotoFileDTO examineesPhotoFileDTO) {
        try {
            String photoUrl = fileUpHwCloudService.uploadFileToHuawei(examineesPhotoFileDTO.getPhotoFile(), "photo", examineesPhotoFileDTO.getPhotoFile().getName());
            return photoUrl;
        } catch (Exception e) {
            throw new InvalidRequestException(43330409, "考生照片上传失败", e);
        }
    }

    /**
     * 验证证件号码是否符合逻辑
     *
     * @param idNo
     * @return
     */
    private String checkIdNoValidity(String idNo) {
        List<String> allGroups = RegexUtils.getMatchers(ID_NO_REGEX, idNo);
        String realIdNo = StrUtil.join("", allGroups);
        return realIdNo;
    }

    /**
     * 获取zip文件中的头像文件
     *
     * @param importFile
     * @return
     * @throws IOException
     */
    private List<CertificateExamineesPhotoFileDTO> getZipFiles(File importFile) throws IOException {
        try (ZipFile zipFile = new ZipFile(importFile, Charset.forName("GBK"))) {
            List<CertificateExamineesPhotoFileDTO> examineesPhotoFileDTOS = Lists.newArrayListWithExpectedSize(zipFile.size());
            Enumeration<? extends ZipEntry> entries = zipFile.entries();
            while (entries.hasMoreElements()) {
                ZipEntry zipEntry = entries.nextElement();
                if (!zipEntry.isDirectory()) {
                    String entryName = zipEntry.getName();
                    List<String> splitFileName = StrUtil.split(entryName, StrUtil.SLASH);
                    if (splitFileName.size() > CommonConstant.ONE) {
                        continue;
                    }

                    InputStream inputStream = zipFile.getInputStream(zipEntry);
                    File file = FileUtil.writeFromStream(inputStream, FileUtil.createTempFile(StrPool.DOT + FileUtil.getSuffix(entryName), Boolean.TRUE));
                    CertificateExamineesPhotoFileDTO photoFileDTO = CertificateExamineesPhotoFileDTO.builder()
                            .idNo(FileUtil.getPrefix(entryName))
                            .photoFile(file)
                            .build();
                    examineesPhotoFileDTOS.add(photoFileDTO);
                }
            }
            return examineesPhotoFileDTOS;
        }
    }

    /**
     * 装配考生拓展信息
     *
     * @param examineesId
     * @param certificateExaminee
     * @param templateConfigs
     * @return
     */
    private List<ZsExamineeExtInfo> assembleExamineesInfoExts(String examineesId, Map<String, String> certificateExaminee, List<ZsTemplateConfig> templateConfigs) {
        Map<String, String> codeMappingName = templateConfigs.stream()
                .collect(Collectors.toMap(ZsTemplateConfig::getVariableCode, ZsTemplateConfig::getVariableName));

        Field[] fields = ReflectUtil.getFields(ZsExamineeInfoDTO.class);
        List<String> templateCodes = Arrays.stream(fields)
                .map(data -> data.getAnnotation(TemplateMapper.class))
                .filter(ArrayUtil::isNotEmpty)
                .map(TemplateMapper::templateCode)
                .flatMap(Arrays::stream)
                .distinct()
                .collect(Collectors.toList());

        return certificateExaminee.entrySet()
                .stream()
                // 过滤掉实体上面存在的Code，实体上存在的Code不进行入库操作
                .filter(data -> !Iterators.contains(templateCodes.iterator(), data.getKey()))
                .map(data -> {
                    ZsExamineeExtInfo zsExamineeExtInfo = ZsExamineeExtInfo.builder()
                            .variableCode(data.getKey())
                            .variableName(codeMappingName.get(data.getKey()))
                            .variableValue(data.getValue())
                            .examineeId(examineesId)
                            .build();
                    zsExamineeExtInfo.setCreateBy(loginService.getUserId())
                            .setCreateDate(LocalDateTime.now())
                            .setUpdateBy(loginService.getUserId())
                            .setUpdateDate(LocalDateTime.now());
                    return zsExamineeExtInfo;
                })
                .collect(Collectors.toList());
    }

    /**
     * 装配考生信息
     *
     * @param certificateExaminee
     * @param zsInfo
     * @return
     */
    private ZsExamineeInfo assembleExamineesInfo(Map<String, String> certificateExaminee, ZsInfo zsInfo) {
        // 装配考生信息DTO
        ZsExamineeInfoDTO examineeInfoDTO = assembleExamineesInfoDTO(certificateExaminee);
        // 获取考生信息Entity
        ZsExamineeInfo examineeInfo = BeanUtil.toBean(examineeInfoDTO, ZsExamineeInfo.class);

        // 获取专业名称
        List<SpecialityDTO> specialityDTOS = templateService.listSpecialities(zsInfo.getCertificateTemplateid());
        String specialityValue = Iterators.tryFind(specialityDTOS.iterator(),
                        data -> StrUtil.equals(data.getSpecialityName(), examineeInfoDTO.getCertificateSubjectName()))
                .toJavaUtil()
                .map(SpecialityDTO::getSpecialityValue)
                .orElseGet(String::new);

        // 获取专业级别
        List<LevelDTO> levelDTOS = templateService.listLevels(specialityValue);
        String levelValue = Iterators.tryFind(levelDTOS.iterator(),
                        data -> StrUtil.equals(data.getLevelName(), examineeInfoDTO.getCertificateLevelDesc()))
                .toJavaUtil()
                .map(LevelDTO::getLevelValue)
                .orElseGet(String::new);

        // 证书有效期结束时间
        Date validityEnd = CertificateValidityType.offsetDate(examineeInfoDTO.getCertificateIssueData(), zsInfo.getCertificateValidityType());
        examineeInfo.setZsId(zsInfo.getId())
                .setExamineeSex(SexEnums.getTypeByDesc(examineeInfoDTO.getExamineeSexDesc()))
                .setIdentificationType(IdentityTypeEnum.getTypeByDesc(examineeInfoDTO.getIdentificationTypeDesc()))
                .setCertificateSubject(specialityValue)
                .setCertificateLevel(levelValue)
                .setCertificateValidityStart(examineeInfoDTO.getCertificateIssueData())
                .setCertificateValidityEnd(validityEnd)
                .setCertificateProvideStatus(CertificateProvideStatusEnums.UNPROVIDED.getCode())
                .setCreateBy(loginService.getUserId())
                .setUpdateBy(loginService.getUserId());

        if (StrUtil.isBlank(examineeInfo.getExamineeSex())) {
            String sexDesc = getSexDescByIdNo(examineeInfo.getIdentificationNo());
            String sexType = SexEnums.getTypeByDesc(sexDesc);
            examineeInfo.setExamineeSex(sexType);
        }
        return examineeInfo;
    }

    /**
     * 装配考生信息DTO
     *
     * @param certificateExaminee
     * @return
     */
    private ZsExamineeInfoDTO assembleExamineesInfoDTO(Map<String, String> certificateExaminee) {
        ZsExamineeInfoDTO examineesInfoDTO = new ZsExamineeInfoDTO();
        Field[] fields = ReflectUtil.getFields(examineesInfoDTO.getClass());
        for (Field field : fields) {
            Optional.ofNullable(field.getAnnotation(TemplateMapper.class))
                    .map(TemplateMapper::templateCode)
                    .map(Arrays::asList)
                    .orElseGet(Lists::newArrayList)
                    .stream()
                    .distinct()
                    .map(certificateExaminee::get)
                    .filter(StrUtil::isNotBlank)
                    .forEach(templateValue -> ReflectUtil.setFieldValue(examineesInfoDTO, field, templateValue));
        }
        return examineesInfoDTO;
    }

    /**
     * 通过模板ID获取模板的配置信息
     *
     * @param certificateTemplateId
     * @return
     */
    private List<ZsTemplateConfig> listTemplateConfigByTemplateId(String certificateTemplateId) {
        String templateId = Optional.ofNullable(certificateTemplateId)
                .filter(StrUtil::isNotBlank)
                .orElseThrow(() -> new CustomException("当前证书未绑定模板"));

        List<ZsTemplateConfig> templateConfigs = templateConfigService.listImportConfigByTemplate(templateId);
        if (CollUtil.isEmpty(templateConfigs)) {
            throw new CustomException("当前证书绑定的模板还未进行规则配置");
        }
        return templateConfigs;
    }

    /**
     * 检查证件号码是否在库中存在
     *
     * @param codeMappingValue
     * @param infoId
     * @return
     */
    private Boolean checkIdNoRepeat(List<Map<String, String>> codeMappingValue, String infoId) {
        long count = codeMappingValue.stream()
                .parallel()
                // 获取到证件号
                .map(data -> data.get(VariableCodeEnums.ID_NO.getCode()))
                .filter(StrUtil::isNotBlank)
                // 通过证件号获取绑定的信息
                .map(idNo -> iZsExamineeInfoService.listByIdNo(idNo, infoId))
                .flatMap(Collection::stream)
                .map(ZsExamineeInfo::getCertificateSerialNumber)
                .filter(StrUtil::isNotBlank)
                .count();
        return count > CommonConstant.ZERO;
    }

    /**
     * 校验证书考生信息
     *
     * @param zsInfo
     * @param codeMappingValues
     * @return
     */
    private List<ImportCertificateExamineesVO> checkCertificateExamineesData(ZsInfo zsInfo,
                                                                             Map<String, ZsTemplateConfig> codeMapTemplateConfigs,
                                                                             List<Map<String, String>> codeMappingValues,
                                                                             BigConsumer<String, Map<String, String>, CheckUtil.CheckHandler>... checkAfterHandlers) {
        Assert.notEmpty(codeMappingValues, "考试证书数据不能为空");

        return codeMappingValues.stream()
                .map(codeMappingValue -> {
                    // 校验考生证书数据
                    CheckUtil.CheckHandler checkHandler = this.validCertificateExamineesData(codeMappingValue, codeMapTemplateConfigs, codeMappingValues, zsInfo);
                    // 执行检查后置处理
                    Optional.ofNullable(checkAfterHandlers)
                            .filter(ArrayUtil::isNotEmpty)
                            .map(Arrays::asList)
                            .orElseGet(Lists::newArrayList)
                            .forEach(checkAfterHandler -> checkAfterHandler.accept(zsInfo.getId(), codeMappingValue, checkHandler));

                    ImportCertificateExamineesVO certificateExamineesVO = ImportCertificateExamineesVO.builder()
                            .validStatus(checkHandler.hasException())
                            .validMessage(checkHandler.allMessageCollect())
                            .errorCodes(checkHandler.allExceptionCodeCollect())
                            .certificateExaminees(codeMappingValue)
                            .build();

                    // 装配导入考生证书数据
                    this.assembleImportCertificateExaminees(certificateExamineesVO, codeMappingValue);

                    return certificateExamineesVO;
                }).collect(Collectors.toList());
    }

    /**
     * 校验证件号码
     *
     * @param codeMappingValue
     * @param checkHandler
     * @param codeMappingValues
     */
    @SuppressWarnings("unchecked")
    private void checkIdNo(Map<String, String> codeMappingValue, CheckUtil.CheckHandler checkHandler, List<Map<String, String>> codeMappingValues, String infoId) {
        Optional.ofNullable(codeMappingValue.get(VariableCodeEnums.ID_TYPE.getCode()))
                .map(IdentificationType::getRulerByDesc)
                .ifPresent(ruler -> {
                    checkHandler.collect(codeMappingValue.get(VariableCodeEnums.ID_NO.getCode()), ruler);
                });

        Map<String, Long> idNoStatistic = codeMappingValues.stream()
                .map(data -> data.get(VariableCodeEnums.ID_NO.getCode()))
                .filter(StrUtil::isNotBlank)
                .collect(Collectors.groupingBy(data -> data, Collectors.counting()));

        Optional.ofNullable(codeMappingValue.get(VariableCodeEnums.ID_NO.getCode()))
                .map(idNoStatistic::get)
                .ifPresent(idNoCount -> {
                    checkHandler.collect(idNoCount, "证件号码重复", Boolean.FALSE, LongRuler.eq((long) CommonConstant.ONE));
                });

        iZsExamineeInfoService.lambdaQuery()
                .eq(ZsExamineeInfo::getZsId, infoId)
                .eq(ZsExamineeInfo::getIdentificationNo, codeMappingValue.get(VariableCodeEnums.ID_NO.getCode()))
                .eq(BaseEntity::getDelFlag, Boolean.FALSE)
                .orderByDesc(BaseEntity::getCreateDate)
                .last("limit 1")
                .oneOpt()
                .map(ZsExamineeInfo::getCertificateProvideStatus)
                .ifPresent(certificateProvideStatus -> {
                    checkHandler.collect(certificateProvideStatus, "证书已颁发", Boolean.FALSE, IntRuler.notEq(CertificateProvideStatusEnums.PROVIDED.getCode()).failCode(-9002));
                });

    }

    /**
     * 校验证书编号
     *
     * @param codeMappingValue
     * @param checkHandler
     * @param zsInfo
     * @param codeMappingValues
     */
    @SuppressWarnings("unchecked")
    private void checkCertificateNo(Map<String, String> codeMappingValue, CheckUtil.CheckHandler checkHandler, ZsInfo zsInfo, List<Map<String, String>> codeMappingValues) {
        Map<String, Long> certificateNoStatistic = codeMappingValues.stream()
                .map(data -> data.get(VariableCodeEnums.CERTIFICATE_NO.getCode()))
                .filter(StrUtil::isNotBlank)
                .collect(Collectors.groupingBy(data -> data, Collectors.counting()));

        Optional.ofNullable(codeMappingValue.get(VariableCodeEnums.CERTIFICATE_NO.getCode()))
                .map(certificateNo -> {
                    String idNo = codeMappingValue.get(VariableCodeEnums.ID_NO.getCode());
                    // 证书编码过长：证书编码字段限制20个字符
                    Ruler certificateRuler = StrRuler.lengthLte(20);
                    checkHandler.collect(certificateNo, VariableCodeEnums.CERTIFICATE_NO.getDesc(), certificateRuler);

                    // 当前证书编码是否已绑定
                    iZsExamineeInfoService.lambdaQuery()
                            .eq(ZsExamineeInfo::getZsId, zsInfo.getId())
                            .eq(ZsExamineeInfo::getCertificateSerialNumber, certificateNo)
                            .eq(BaseEntity::getDelFlag, Boolean.FALSE)
                            .oneOpt()
                            // 获取库中的证件号
                            .map(ZsExamineeInfo::getIdentificationNo)
                            .filter(StrUtil::isNotBlank)
                            .ifPresent(identificationNo -> {
                                checkHandler.collect(identificationNo, "证书编码已绑定其余人员", Boolean.FALSE, StrRuler.eq(idNo).failCode(-9003));
                            });
                    return certificateNoStatistic.get(certificateNo);
                })
                .ifPresent(certificateNoCount -> {
                    checkHandler.collect(certificateNoCount, "证书编码重复", Boolean.FALSE, LongRuler.eq((long) CommonConstant.ONE));
                });
    }

    /**
     * 校验考生证书数据
     *
     * @param codeMappingValue
     * @param codeMappingValues
     * @param zsInfo
     * @return
     */
    @SuppressWarnings("unchecked")
    private CheckUtil.CheckHandler validCertificateExamineesData(Map<String, String> codeMappingValue, Map<String, ZsTemplateConfig> codeMapTemplateConfigs, List<Map<String, String>> codeMappingValues, ZsInfo zsInfo) {
        CheckUtil.CheckHandler checkHandler = CheckUtil.none();
        codeMappingValue.forEach((code, value) -> {
            // 获取code对应的templateConfig
            Optional.ofNullable(codeMapTemplateConfigs.get(code))
                    .ifPresent(templateConfig -> {
                        // 装配templateConfig对应的校验处理器
                        checkHandler.collect(value,
                                templateConfig.getVariableName(),
                                ImportCheckTypeEnums.getRulerByCode(templateConfig.getImportCheckType()));
                    });
        });

        // 校验证件号码
        this.checkIdNo(codeMappingValue, checkHandler, codeMappingValues, zsInfo.getId());
        // 校验证书编号
        this.checkCertificateNo(codeMappingValue, checkHandler, zsInfo, codeMappingValues);
        return checkHandler;
    }

    /**
     * 装配导入考生证书数据
     *
     * @param certificateExamineesVO
     * @param codeMappingValue
     * @return
     */
    private ImportCertificateExamineesVO assembleImportCertificateExaminees(ImportCertificateExamineesVO certificateExamineesVO, Map<String, String> codeMappingValue) {
        Class<? extends ImportCertificateExamineesVO> clazz = certificateExamineesVO.getClass();
        Field[] fields = ReflectUtil.getFields(clazz);
        for (Field field : fields) {
            Optional.ofNullable(field.getAnnotation(TemplateMapper.class))
                    .map(TemplateMapper::templateCode)
                    .map(ListUtil::toList)
                    .orElseGet(Lists::newArrayList)
                    .forEach(templateCode -> {
                        Optional.ofNullable(codeMappingValue.get(templateCode))
                                .filter(StrUtil::isNotBlank)
                                .ifPresent(codeValue -> {
                                    ReflectUtil.setFieldValue(certificateExamineesVO, field, codeValue);
                                });
                    });
        }

        assembleExamineesSex(certificateExamineesVO);

        return certificateExamineesVO;
    }

    /**
     * 设置性别
     *
     * @param certificateExamineesVO
     */
    private void assembleExamineesSex(ImportCertificateExamineesVO certificateExamineesVO) {
        String sexDescByIdNo = getSexDescByIdNo(certificateExamineesVO.getIdentificationNo());
        certificateExamineesVO.setExamineesSex(sexDescByIdNo);
    }

    /**
     * 获取性别描述通过证件号码
     *
     * @param idNo
     * @return
     */
    private String getSexDescByIdNo(String idNo) {
        try {
            // 从15、18位身份证上获取性别
            int genderByIdCard = IdcardUtil.getGenderByIdCard(idNo);
            return SexEnums.getDescByType(String.valueOf(genderByIdCard));
        } catch (Exception e) {
            try {
                // 从港澳台证件号上获取性别
                String[] validCard10 = IdcardUtil.isValidCard10(idNo);
                String genderCode = ArrayUtil.get(validCard10, CommonConstant.ONE);
                return GATSexEnums.getDescByType(genderCode);
            } catch (Exception ce) {

            }
        }
        return null;
    }

    /**
     * 获取excel中的数据
     *
     * @param inputStream
     * @return
     */
    private List<Map<String, String>> getExcelDataList(InputStream inputStream, Map<String, ZsTemplateConfig> codeMapTemplateConfigs) {
        Collection<ZsTemplateConfig> templateConfigs = codeMapTemplateConfigs.values();
        
        // code -> value(name->yhk,idtype->身份证
        List<Map<String, String>> cachedDataList = Lists.newCopyOnWriteArrayList();
        EasyExcel.read(inputStream, new AnalysisEventListener<Map<Integer, String>>() {
            //index -> code
            final Map<Integer, String> headMap = Maps.newConcurrentMap();

            @Override
            public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
                log.info("# 导入证书人员数据Excel表头：{}", JSON.toJSONString(headMap));

                if (MapUtil.isEmpty(headMap)) {
                    throw new CustomException("导入证书人员数据的未解析出表头");
                }

                Map<String, String> nameMappingCode = templateConfigs.stream()
                        .collect(Collectors.toMap(ZsTemplateConfig::getVariableName, ZsTemplateConfig::getVariableCode));

                // 通过模板配置项校验
                nameMappingCode.forEach((key, value) -> {
                    if (!headMap.containsValue(key)) {
                        throw new CustomException("导入证书人员数据文件表头错误");
                    }
                });

                // 通过表头校验
                headMap.forEach((key, value) -> {
                    String code = Optional.ofNullable(value)
                            .map(nameMappingCode::get)
                            .orElseThrow(() -> new CustomException("导入证书人员数据的表头错误"));
                    this.headMap.put(key, code);
                });
            }

            @Override
            public void invoke(Map<Integer, String> data, AnalysisContext context) {
                log.info("# 导入证书人员数据Excel解析行数据:{}", JSON.toJSONString(data));
                Map<String, String> certificateExamineesData = Maps.newHashMap();
                headMap.forEach((key, value) -> {
                    certificateExamineesData.put(value, data.get(key));
                });
                cachedDataList.add(certificateExamineesData);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                log.info("# 导入证书人员数据所有数据解析完成！");
                if (log.isDebugEnabled()) {
                    log.debug("# 导入证书人员数据所有解析数据：{}", FastJsonUtil.getBeanToJson(cachedDataList));
                }
            }

        }).sheet().doRead();
        return cachedDataList;
    }

    @SneakyThrows
    private void batchCreateCertificate(ZsInfo zsInfo, ZsTemplate zsTemplate, List<ZsTemplateConfig> zsTemplateConfigs, String updateBy, String consumerId) {
        List<ZsExamineeInfo> examineesInfos = iZsExamineeInfoService.releasableExamineesInfo(zsInfo.getId());
        ProgressManager.updateProgressCurrent(25, StrUtil.format("查询出{}个需要生成证书的考生", CollUtil.size(examineesInfos)));

        List<List<ZsExamineeInfo>> partition = Lists.partition(examineesInfos, globalConfig.getPartitionSize());
        ProgressManager.updateProgressCurrent(30, StrUtil.format("拆分出{}个证书生成子任务", CollUtil.size(partition)));

        CountDownLatch countDownLatch = new CountDownLatch(partition.size());
        for (List<ZsExamineeInfo> zsExamineesInfos : partition) {
            ProgressManager.getProgressTaskExecutor().execute(() -> {
                batchCreateCertificate(zsTemplate, zsTemplateConfigs, zsExamineesInfos, updateBy);
                //当前线程调用此方法，则计数减一
                countDownLatch.countDown();
                ProgressManager.stepProgressCurrent((int) (partition.size() - countDownLatch.getCount()), partition.size(), 60, 90, StrUtil.format("批量生成证书还剩{}个子任务还未完成", countDownLatch.getCount()));
            });
        }
        countDownLatch.await();

    }

    private void batchCreateCertificate(ZsTemplate zsTemplate, List<ZsTemplateConfig> zsTemplateConfigs, List<ZsExamineeInfo> zsExamineeInfos, String updateBy) {
        for (ZsExamineeInfo zsExamineeInfo : zsExamineeInfos) {
            try {
                iZsExamineeInfoService.issueCertificate(zsExamineeInfo, zsTemplate, zsTemplateConfigs, updateBy);
            } catch (Exception e) {
                log.error("# 批量创建考生证书异常：" + FastJsonUtil.getBeanToJson(zsExamineeInfo), e);
            }
        }
    }

    /**
     * 导出证书人员导入模板
     *
     * @param sheetName
     * @param templateConfigs
     * @param selectMap
     * @param response
     * @throws IOException
     */
    public void exportCertificateTemplate(String sheetName, List<ZsTemplateConfig> templateConfigs, Map<String, List<String>> selectMap, HttpServletResponse response) throws IOException {
        ServletOutputStream outputStream = response.getOutputStream();

        List<String> titleList = templateConfigs.stream()
                .map(ZsTemplateConfig::getVariableName)
                .collect(Collectors.toList());

        //创建工作簿对象
        Workbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet(sheetName);
        sheet.setDefaultColumnWidth(globalConfig.getDefaultColumnWidth());
        Row row = sheet.createRow(CommonConstant.ZERO);
        for (int i = 0; i < titleList.size(); i++) {
            String title = CollUtil.get(titleList, i);
            Cell cell = row.createCell(i);
            cell.setCellValue(title);
        }
        //创建隐藏目录
        createHideSheet(wb, selectMap);

        assembleDataValidation(sheet, templateConfigs, selectMap);

        String lastFileName = sheetName + ".xls";
        response.setContentType("application/msexcel;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(lastFileName, "UTF-8"));
        log.debug("# 下载证书人员导入模板文件生成成功进行响应");
        // 节点流来自servlet，处理流不进行关闭
        wb.write(outputStream);
        IoUtil.flush(outputStream);
    }

    /**
     * 下拉框数据装配
     *
     * @param sheet
     * @param templateConfigs
     * @param selectMap
     */
    private void assembleDataValidation(Sheet sheet, List<ZsTemplateConfig> templateConfigs, Map<String, List<String>> selectMap) {
        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();

        Iterators.tryFind(templateConfigs.iterator(),
                        data -> StrUtil.equals(globalConfig.getSpecialityType(), data.getVariableCode()))
                .toJavaUtil()
                .ifPresent(specialityConfig -> {
                    int specialityIndex = Iterators.indexOf(templateConfigs.iterator(),
                            data -> StrUtil.equals(specialityConfig.getVariableCode(), data.getVariableCode()));
                    // 如果专业的excel位置不是如上tileList中的位置，需要变更则需要INDIRECT中所在的列名称
                    // 专业规则
                    DataValidationConstraint specialityConstraint = dataValidationHelper.createExplicitListConstraint(ArrayUtil.toArray(selectMap.keySet(), String.class));
                    CellRangeAddressList specialityRangeAddressList = new CellRangeAddressList(CommonConstant.ONE, globalConfig.getXlsMaxRow(), specialityIndex, specialityIndex);
                    DataValidation specialityDataValidation = dataValidationHelper.createValidation(specialityConstraint, specialityRangeAddressList);
                    // 设置输入信息提示信息
                    specialityDataValidation.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的证书专业！");
                    // 设置输入错误提示信息
                    specialityDataValidation.createErrorBox("选择错误提示", "你输入的值未在备选列表中，请下拉选择合适的证书专业！");
                    specialityDataValidation.setShowErrorBox(true);
                    sheet.addValidationData(specialityDataValidation);

                    Iterators.tryFind(templateConfigs.iterator(),
                                    data -> StrUtil.equals(globalConfig.getLevelCode(), data.getVariableCode()))
                            .toJavaUtil()
                            .ifPresent(levelConfig -> {
                                int levelIndex = Iterators.indexOf(templateConfigs.iterator(),
                                        data -> StrUtil.equals(levelConfig.getVariableCode(), data.getVariableCode()));
                                // 级别规则
                                CellRangeAddressList levelRangeAddressList = new CellRangeAddressList(CommonConstant.ONE, globalConfig.getXlsMaxRow(), levelIndex, levelIndex);
                                String listFormula = StrUtil.format("INDIRECT(${}1)", ExcelUtils.digit2ExcelNum(specialityIndex + CommonConstant.ONE));
                                DataValidation levelDataValidation = dataValidationHelper.createValidation(dataValidationHelper.createFormulaListConstraint(listFormula), levelRangeAddressList);
                                // 设置输入信息提示信息
                                levelDataValidation.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的证书级别！");
                                // 设置输入错误提示信息
                                levelDataValidation.createErrorBox("选择错误提示", "你输入的值未在备选列表中，请下拉选择合适的证书级别！");
                                levelDataValidation.setShowErrorBox(true);
                                sheet.addValidationData(levelDataValidation);
                            });
                });

        Iterators.tryFind(templateConfigs.iterator(),
                        data -> StrUtil.equals(globalConfig.getSexCode(), data.getVariableCode()))
                .toJavaUtil()
                .ifPresent(sexConfig -> {
                    int sexIndex = templateConfigs.indexOf(sexConfig);
                    DataValidationConstraint sexConstraint = dataValidationHelper.createExplicitListConstraint(SexEnums.allDesc());
                    CellRangeAddressList sexRangeAddressList = new CellRangeAddressList(CommonConstant.ONE, globalConfig.getXlsMaxRow(), sexIndex, sexIndex);
                    DataValidation sexDataValidation = dataValidationHelper.createValidation(sexConstraint, sexRangeAddressList);
                    // 设置输入信息提示信息
                    sexDataValidation.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的性别！");
                    // 设置输入错误提示信息
                    sexDataValidation.createErrorBox("选择错误提示", "你输入的值未在备选列表中，请下拉选择合适的性别！");
                    sexDataValidation.setShowErrorBox(true);
                    sheet.addValidationData(sexDataValidation);
                });

        Iterators.tryFind(templateConfigs.iterator(),
                        data -> StrUtil.equals(globalConfig.getIdType(), data.getVariableCode()))
                .toJavaUtil()
                .ifPresent(idTypeConfig -> {
                    int idTypeIndex = templateConfigs.indexOf(idTypeConfig);
                    DataValidationConstraint idTypeConstraint = dataValidationHelper.createExplicitListConstraint(IdentityTypeEnum.allDesc());
                    CellRangeAddressList idTypeRangeAddressList = new CellRangeAddressList(CommonConstant.ONE, globalConfig.getXlsMaxRow(), idTypeIndex, idTypeIndex);
                    DataValidation idTypeDataValidation = dataValidationHelper.createValidation(idTypeConstraint, idTypeRangeAddressList);
                    // 设置输入信息提示信息
                    idTypeDataValidation.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的证件类型！");
                    // 设置输入错误提示信息
                    idTypeDataValidation.createErrorBox("选择错误提示", "你输入的值未在备选列表中，请下拉选择合适的证件类型！");
                    idTypeDataValidation.setShowErrorBox(true);
                    sheet.addValidationData(idTypeDataValidation);
                });

        Iterators.tryFind(templateConfigs.iterator(),
                        data -> StrUtil.equals(VariableCodeEnums.SEX.getCode(), data.getVariableCode()))
                .toJavaUtil()
                .ifPresent(sexConfig -> {
                    int sexIndex = Iterators.indexOf(templateConfigs.iterator(),
                            data -> StrUtil.equals(sexConfig.getVariableCode(), data.getVariableCode()));
                    DataValidationConstraint sexConstraint = dataValidationHelper.createExplicitListConstraint(SexEnums.allDesc());
                    CellRangeAddressList sexRangeAddressList = new CellRangeAddressList(CommonConstant.ONE, globalConfig.getXlsMaxRow(), sexIndex, sexIndex);
                    DataValidation sexDataValidation = dataValidationHelper.createValidation(sexConstraint, sexRangeAddressList);
                    // 设置输入信息提示信息
                    sexDataValidation.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的性别！");
                    // 设置输入错误提示信息
                    sexDataValidation.createErrorBox("选择错误提示", "你输入的值未在备选列表中，请下拉选择合适的性别！");
                    sexDataValidation.setShowErrorBox(true);
                    sheet.addValidationData(sexDataValidation);
                });

        Iterators.tryFind(templateConfigs.iterator(),
                        data -> StrUtil.equals(VariableCodeEnums.ISSUE_DATE.getCode(), data.getVariableCode()))
                .toJavaUtil()
                .ifPresent(issueDateConfig -> {
                    int issueDateIndex = Iterators.indexOf(templateConfigs.iterator(),
                            data -> StrUtil.equals(issueDateConfig.getVariableCode(), data.getVariableCode()));
                    // 检查的区域   new CellRangeAddressList(-1, -1, 3, 4);  表示第四行整列
                    CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(CommonConstant.ONE, globalConfig.getXlsMaxRow(), issueDateIndex, issueDateIndex);
                    DataValidationHelper helper = sheet.getDataValidationHelper();
                    //DVConstraint constrain1 = DVConstraint.CreateDateConstraint(条件,"最小时间","最大时间","时间格式"); //这是检查时间的方法
                    DataValidationConstraint constraint = helper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN, "1900-1-1", "2099-12-31", "yyyy-MM-dd");
                    DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
                    // 输入无效值时是否显示错误框
                    dataValidation.setShowErrorBox(true);
                    // 验证输入数据是否真确
                    dataValidation.setSuppressDropDownArrow(true);
                    // 设置无效值时 是否弹出提示框
                    dataValidation.setShowPromptBox(true);
                    // 设置提示框内容 createPromptBox
                    // 设置无效值时的提示框内容 createErrorBox
                    dataValidation.createPromptBox("温馨提示", "请输入[yyyy/MM/dd]格式日期！！！");
                    sheet.addValidationData(dataValidation);
                });

        // 日期时间格式
        Iterators.tryFind(templateConfigs.iterator(),
                        data -> NumberUtil.equals(data.getImportCheckType(), ImportCheckTypeEnums.DATE.getCode()))
                .toJavaUtil()
                .ifPresent(dateConfig -> {
                    int dateIndex = templateConfigs.indexOf(dateConfig);
                    // 检查的区域   new CellRangeAddressList(-1, -1, 3, 4);  表示第四行整列
                    CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(CommonConstant.ONE, globalConfig.getXlsMaxRow(), dateIndex, dateIndex);
                    DataValidationHelper helper = sheet.getDataValidationHelper();
                    //DVConstraint constrain1 = DVConstraint.CreateDateConstraint(条件,"最小时间","最大时间","时间格式"); //这是检查时间的方法
                    DataValidationConstraint constraint = helper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN, "1900-1-1", "2099-12-31", "yyyy-MM-dd");
                    DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
                    // 输入无效值时是否显示错误框
                    dataValidation.setShowErrorBox(true);
                    // 验证输入数据是否真确
                    dataValidation.setSuppressDropDownArrow(true);
                    // 设置无效值时 是否弹出提示框
                    dataValidation.setShowPromptBox(true);
                    // 设置提示框内容 createPromptBox
                    // 设置无效值时的提示框内容 createErrorBox
                    dataValidation.createPromptBox("温馨提示", "请输入[yyyy/MM/dd]格式日期！！！");
                    sheet.addValidationData(dataValidation);
                });
    }

    /**
     * 创建隐藏页
     *
     * @param wb
     * @param selectMap
     */
    public static void createHideSheet(Workbook wb, Map<String, List<String>> selectMap) {
        //创建一个专门用来存放专业证书等级信息的隐藏sheet页
        Sheet hideSheet = wb.createSheet("speciality");
        Set<String> specialityNames = selectMap.keySet();
        int rowId = CommonConstant.ZERO;
        // 将具体的数据写入到每一行中，行开头为专业名称，后面是级别名称。
        for (int i = 0; i < specialityNames.size(); i++) {
            String specialityName = CollUtil.get(specialityNames, i);
            List<String> levelNames = selectMap.get(specialityName);

            Row row = hideSheet.createRow(rowId++);
            row.createCell(CommonConstant.ZERO).setCellValue(specialityName);

            for (int j = 0; j < levelNames.size(); j++) {
                Cell cell = row.createCell(j + CommonConstant.ONE);
                cell.setCellValue(CollUtil.get(levelNames, j));
            }
            // 添加名称管理器
            String range = ExcelUtils.getRange(CommonConstant.ONE, rowId, levelNames.size());
            Name name = wb.createName();
            //key不可重复
            name.setNameName(specialityName);
            String formula = "speciality!" + range;
            name.setRefersToFormula(formula);
        }
        wb.setSheetHidden(CommonConstant.ONE, true);
    }


    /**
     * @Params:
     * @Description: 创建证书
     * @Author: STARF
     * @CreateDate: 2022/6/21 16:08
     **/
    @Override
    @Transactional(rollbackFor = Exception.class)
    public void createCertificate(ZsInfoDTO zsInfoDTO) {
        LocalDateTime time = LocalDateTime.now();
        zsInfoDTO.setId(UUIDUtils.newSortUUID());
        ZsInfo zsInfo = BeanUtil.toBean(zsInfoDTO, ZsInfo.class);
        zsInfo.setCreateDate(time);
        zsInfo.setUpdateDate(time);
        zsInfo.setDelFlag(false);
        zsInfo.setCertificateStatus(CertificateStatusEnums.UNPUBLISHED.getCode());
        zsInfoMapper.insert(zsInfo);

        // 关联成绩数据
        if (null != zsInfoDTO.getCertificateDataSource() && CertificateDataSourceEnums.RELATE_CJ.getCode().equals(zsInfoDTO.getCertificateDataSource())) {
            log.info("# 创建证书当前为关联成绩数据：{}，进行考生同步！", zsInfoDTO.getCertificateName());
            this.zsExamGrade(zsInfoDTO);
        }
    }

    /**
     * @Params:
     * @Description: 编辑证书
     * @Author: STARF
     * @CreateDate: 2022/6/21 16:32
     **/
    @Override
    @Transactional(rollbackFor = Exception.class)
    public void editCertificate(ZsInfoDTO zsInfoDTO) {
        if (StringUtils.isEmpty(zsInfoDTO.getId())) {
            return;
        }

        ZsInfo zsInfo = zsInfoMapper.selectById(zsInfoDTO.getId());
        if (null == zsInfo) {
            return;
        }

        //证书发布状态为已发布的证书，不允许编辑
        if (CertificateStatusEnums.PUBLISHED.getCode().equals(zsInfo.getCertificateStatus())) {
            return;
        }

        LocalDateTime time = LocalDateTime.now();
        Integer oldDataSource = zsInfo.getCertificateDataSource();
        String oldExamId = zsInfo.getExamId();
        Integer oldGrantObject = zsInfo.getCertificateGrantObject();
        String oldSubject = zsInfo.getCertificateSubject();
        String oldLevel = zsInfo.getCertificateLevel();
        String oldExamScoreLines = zsInfo.getExamScoreLines();
        Integer oldCertificateValidityType = zsInfo.getCertificateValidityType();
        String oldCertificateTemplateid = zsInfo.getCertificateTemplateid();
        Integer oldCertificatePublishType = zsInfo.getCertificatePublishType();

        BeanUtils.copyProperties(zsInfoDTO, zsInfo);
        if (null != zsInfoDTO.getCertificateGrantObject() && CertificateGrantObjectEnums.ARCHIVED.getCode().equals(zsInfoDTO.getCertificateGrantObject())) {
            zsInfo.setExamScoreLines("");
        }
        zsInfo.setUpdateDate(time);
        zsInfo.setDelFlag(false);
        zsInfoMapper.updateById(zsInfo);


        //考生来源变动，删除原来的
        Boolean dataSourceChange = !oldDataSource.equals(zsInfoDTO.getCertificateDataSource());
        Boolean certificatePublishTypeChange = !NumberUtil.equals(oldCertificatePublishType, zsInfoDTO.getCertificatePublishType());
        Boolean subjectChange = !oldSubject.equals(zsInfoDTO.getCertificateSubject());
        Boolean examChange = !oldExamId.equals(zsInfoDTO.getExamId());
        Boolean grantObjectChange = Boolean.FALSE;
        Boolean certificateValidityTypeChange = Boolean.FALSE;
        Boolean certificateTemplateIdChange = Boolean.FALSE;
        if (null == oldGrantObject && null != zsInfoDTO.getCertificateGrantObject()) {
            grantObjectChange = Boolean.TRUE;
        }
        if (null != oldGrantObject && null != zsInfoDTO.getCertificateGrantObject()) {
            grantObjectChange = !oldGrantObject.equals(zsInfoDTO.getCertificateGrantObject());
        }
        Boolean levelChange = !oldLevel.equals(zsInfoDTO.getCertificateLevel());
        Boolean socreLinesChange = Boolean.FALSE;
        // 考试结果是否发生变化
        if (StringUtils.isEmpty(oldExamScoreLines) && StringUtils.isNotEmpty(zsInfoDTO.getExamScoreLines())) {
            socreLinesChange = Boolean.TRUE;
        }
        // 证书有效期是否发生变更
        if (!oldCertificateValidityType.equals(zsInfoDTO.getCertificateValidityType())) {
            certificateValidityTypeChange = Boolean.TRUE;
        }
        if (StringUtils.isNotEmpty(oldCertificateTemplateid) && StringUtils.isNotEmpty(zsInfoDTO.getCertificateTemplateid()) && !oldCertificateTemplateid.equals(zsInfoDTO.getCertificateTemplateid())) {
            certificateTemplateIdChange = Boolean.TRUE;
        }
        if (StringUtils.isNotEmpty(oldExamScoreLines) && StringUtils.isNotEmpty(zsInfoDTO.getExamScoreLines())) {
            List<String> oldScoreLines = Arrays.asList(oldExamScoreLines.split(","));
            List<String> newScoreLines = Arrays.asList(zsInfoDTO.getExamScoreLines().split(","));
            // 比较两个集合里面的元素是不是一致，不考虑顺序
            if (oldScoreLines.size() != newScoreLines.size()) {
                socreLinesChange = Boolean.TRUE;
            } else {
                Collections.sort(oldScoreLines);
                Collections.sort(newScoreLines);
                String oldScoreLinesStr = String.join(",", oldScoreLines);
                String newScoreLinesStr = String.join(",", newScoreLines);
                socreLinesChange = !oldScoreLinesStr.equals(newScoreLinesStr);
            }
        }

        if (dataSourceChange || certificatePublishTypeChange || examChange || grantObjectChange || subjectChange || levelChange || socreLinesChange || certificateTemplateIdChange) {
            UpdateWrapper<ZsExamineeInfo> updateWrapper = new UpdateWrapper<>();
            updateWrapper.lambda()
                    .eq(ZsExamineeInfo::getZsId, zsInfoDTO.getId())
                    .set(ZsExamineeInfo::getDelFlag, true)
                    .set(ZsExamineeInfo::getUpdateBy, zsInfoDTO.getUpdateBy())
                    .set(ZsExamineeInfo::getUpdateDate, time);
            iZsExamineeInfoService.update(updateWrapper);
            // 删除掉原有的考生关联信息
            enterpriseExamineeCertificateRelationService.lambdaUpdate()
                    .eq(EnterpriseExamineeCertificateRelation::getZsInfoId, zsInfoDTO.getId())
                    .remove();

            // 重新关联考生
            if (null != zsInfoDTO.getCertificateDataSource() && CertificateDataSourceEnums.RELATE_CJ.getCode().equals(zsInfoDTO.getCertificateDataSource())) {
                this.zsExamGrade(zsInfoDTO);
            }
        }

        if (certificateValidityTypeChange) {
            zsExamineeValidatiyEndChange(zsInfoDTO);
        }
    }

    /**
     * 证书有效期时间变更进行批量修改
     *
     * @param zsInfoDTO
     */
    private void zsExamineeValidatiyEndChange(ZsInfoDTO zsInfoDTO) {
        ExecutorPoolUtil.getPoolTaskExecutor().submit(() -> {
            log.info("# 开始同步修改证书下考生证书的有效期：{}", zsInfoDTO.getCertificateName());
            List<ZsExamineeInfo> zsExamineeInfos = iZsExamineeInfoService.allExamineesInfo(zsInfoDTO.getId());
            if (CollectionUtils.isNotEmpty(zsExamineeInfos)) {
                for (ZsExamineeInfo zsExamineeInfo : zsExamineeInfos) {
                    Date validityEnd = CertificateValidityType.offsetDate(zsExamineeInfo.getCertificateValidityStart(), zsInfoDTO.getCertificateValidityType());
                    zsExamineeInfo.setCertificateValidityEnd(validityEnd);
                }
                iZsExamineeInfoService.updateBatchById(zsExamineeInfos);
            }
        });

    }

    /**
     * @Params:
     * @Description: 证书关联成绩
     * @Author: STARF
     * @CreateDate: 2022/6/22 15:50
     **/
    public void zsExamGrade(ZsInfoDTO zsInfoDTO) {
        ExecutorPoolUtil.getPoolTaskExecutor().submit(() -> {
            log.info("# 创建证书关联成绩考生绑定，证书名称：{}", zsInfoDTO.getCertificateName());
            List<EnterpriseExamineeInfo> list = new ArrayList<>();
            //导入已归档的考生
            if (CertificateGrantObjectEnums.ARCHIVED.getCode().equals(zsInfoDTO.getCertificateGrantObject())) {
                EnterpriseExamineeInfo enterpriseExamineeInfo = new EnterpriseExamineeInfo();
                enterpriseExamineeInfo.setExamId(zsInfoDTO.getExamId());
                enterpriseExamineeInfo.setArchivedStatus("Y");
                list = enterpriseExamineeInfoMapper.selectEnterpriseExamineeInfoList(enterpriseExamineeInfo);
            } else if (CertificateGrantObjectEnums.EXAM_GRADE.getCode().equals(zsInfoDTO.getCertificateGrantObject())) {
                // 装配分数线考生数据
                list = assembleCertificateExamGradeData(zsInfoDTO);
            }

            operatorCertificateExaminee(zsInfoDTO, list);
        });
    }

    /**
     * 操作证书考生入库、绑定关系
     *
     * @param zsInfoDTO
     * @param list
     */
    private void operatorCertificateExaminee(ZsInfoDTO zsInfoDTO, List<EnterpriseExamineeInfo> list) {
        LocalDateTime time = LocalDateTime.now();

        List<ZsExamineeInfo> zsList = new ArrayList<>();
        List<EnterpriseExamineeCertificateRelation> examineeCertificateRelationSaveList = new ArrayList<>();
        for (EnterpriseExamineeInfo info : list) {
            if (StringUtils.isEmpty(info.getIdcard())) {
                log.error("# 创建证书关联成绩考生绑定，生成考生证件为空：{}", info.getAdmissionNo());
                continue;
            }
            log.debug("# 创建证书关联成绩考生绑定，生成考生：{}", info.getName());
            ZsExamineeInfo zsExamineeInfo = new ZsExamineeInfo();
            zsExamineeInfo.setId(UUIDUtils.newSortUUID());
            zsExamineeInfo.setZsId(zsInfoDTO.getId());
            zsExamineeInfo.setExamineeName(info.getName());
            zsExamineeInfo.setExamineeSex(info.getSex());
            zsExamineeInfo.setIdentificationType(IdentificationType.IDCARD.getCode());
            zsExamineeInfo.setIdentificationNo(info.getIdcard());
            zsExamineeInfo.setAdmissionNo(info.getAdmissionNo());
            zsExamineeInfo.setCertificateSubject(zsInfoDTO.getCertificateSubject());
            zsExamineeInfo.setCertificateSubjectName(zsInfoDTO.getCertificateSubjectDesc());
            zsExamineeInfo.setCertificateLevel(zsInfoDTO.getCertificateLevel());
            zsExamineeInfo.setCertificateLevelDesc(zsInfoDTO.getCertificateLevelDesc());
            zsExamineeInfo.setCertificateProvideStatus(CertificateProvideStatusEnums.UNPROVIDED.getCode());
            zsExamineeInfo.setCreateBy(zsInfoDTO.getCreateBy());
            zsExamineeInfo.setUpdateBy(zsInfoDTO.getUpdateBy());
            zsExamineeInfo.setCreateDate(time);
            zsExamineeInfo.setUpdateDate(time);
            zsExamineeInfo.setDelFlag(false);

            // 企业考生证书关联
            EnterpriseExamineeCertificateRelation examineeCertificateRelation = EnterpriseExamineeCertificateRelation
                    .builder()
                    .id(UUIDUtils.newSortUUID())
                    .enterpriseExamineeInfoId(info.getId())
                    .examInfoId(info.getExamId())
                    .zsInfoId(zsInfoDTO.getId())
                    .zsExamineeInfoId(zsExamineeInfo.getId())
                    .build();

            zsList.add(zsExamineeInfo);
            examineeCertificateRelationSaveList.add(examineeCertificateRelation);
        }
        iZsExamineeInfoService.saveBatch(zsList);

        this.synchronizeAllExamineeCertificateRelation(zsInfoDTO, examineeCertificateRelationSaveList);
    }

    /**
     * 装配分数线考生数据
     *
     * @param zsInfoDTO
     * @return
     */
    private List<EnterpriseExamineeInfo> assembleCertificateExamGradeData(ZsInfoDTO zsInfoDTO) {
        //考试结果
        EnterpriseScoreLineListParam result = enterpriseExamScoreLineSettingService.getScoreLineVOList(zsInfoDTO.getExamId(), "0", null);
        if (CollectionUtils.isEmpty(result.getScoreLineData())) {
            return Lists.newArrayList();
        }
        List<String> qualifiedStatus = new ArrayList<String>();
        List<String> regionNames = new ArrayList<String>();
        if (CommonEnums.SCORE_LINE_TYPE_0.getCode().equals(result.getType()) || CommonEnums.SCORE_LINE_TYPE_1.getCode().equals(result.getType())) {
            List<String> scoreIdList = Arrays.asList(zsInfoDTO.getExamScoreLines().split(","));
            if (CollectionUtils.isNotEmpty(scoreIdList)) {
                for (String scoreId : scoreIdList) {
                    qualifiedStatus.add(scoreId);
                }
            }
        }

        if (CommonEnums.SCORE_LINE_TYPE_2.getCode().equals(result.getType())) {
            List<String> scoreIdList = Arrays.asList(zsInfoDTO.getExamScoreLines().split(","));
            if (CollectionUtils.isNotEmpty(scoreIdList)) {
                for (String scoreId : scoreIdList) {
                    regionNames.add(scoreId);
                }
            }
        }

        QueryWrapper<EnterpriseExamineeInfo> queryWrapper = new QueryWrapper<>();
        queryWrapper.lambda().eq(EnterpriseExamineeInfo::getExamId, zsInfoDTO.getExamId()).eq(EnterpriseExamineeInfo::getArchivedStatus, "Y");
        if (CollUtil.isNotEmpty(qualifiedStatus)) {
            queryWrapper.lambda().in(EnterpriseExamineeInfo::getQualifiedStatus, qualifiedStatus);
        }
        if (CollUtil.isNotEmpty(regionNames)) {
            queryWrapper.lambda().in(EnterpriseExamineeInfo::getRegionName, regionNames);
        }
        return enterpriseExamineeInfoMapper.selectList(queryWrapper);
    }

    /**
     * 同步考生证书关系
     *
     * @param zsInfoDTO
     * @param examineeCertificateRelationSaveList
     */
    private void synchronizeAllExamineeCertificateRelation(ZsInfoDTO zsInfoDTO, List<EnterpriseExamineeCertificateRelation> examineeCertificateRelationSaveList) {
        // 删除掉原有的考生关联信息
        enterpriseExamineeCertificateRelationService.lambdaUpdate()
                .eq(EnterpriseExamineeCertificateRelation::getZsInfoId, zsInfoDTO.getId())
                .remove();
        if (NumberUtil.equals(zsInfoDTO.getCertificatePublishType(), CertificatePublishTypeEnums.RELATION_CJ_PUBLISH.getCode())) {
            enterpriseExamineeCertificateRelationService.saveBatch(examineeCertificateRelationSaveList);
        }
    }

    /**
     * 同步考生证书关系
     *
     * @param zsInfoDTO
     * @param examineeCertificateRelationSaveList
     */
    @Override
    public void synchronizeListExamineeCertificateRelation(ZsInfoDTO zsInfoDTO, List<EnterpriseExamineeCertificateRelation> examineeCertificateRelationSaveList) {
        if (NumberUtil.equals(zsInfoDTO.getCertificatePublishType(), CertificatePublishTypeEnums.RELATION_CJ_PUBLISH.getCode())) {
            enterpriseExamineeCertificateRelationService.saveBatch(examineeCertificateRelationSaveList);
        }
    }

    /**
     * @Params:
     * @Description: 删除证书
     * @Author: STARF
     * @CreateDate: 2022/6/21 16:34
     **/
    @Override
    public void deleteCertificate(String id, String deleteBy) {
        ZsInfo zsInfo = zsInfoMapper.selectById(id);
        if (null == zsInfo) {
            throw new CustomException("无效的证书");
        }

        // 证书已发布不可删除
        if (CertificateStatusEnums.PUBLISHED.getCode().equals(zsInfo.getCertificateStatus())) {
            throw new CustomException("证书已发布不可删除");
        }

        // 证书发布状态为已发布的证书，不允许删除
        Integer countPublishNumByZsId = iZsExamineeInfoService.countPublishNumByZsId(id, CertificateProvideStatusEnums.PROVIDED.getCode());
        if (countPublishNumByZsId > CommonConstant.ZERO) {
            throw new CustomException("已发放证书状态不可删除");
        }

        zsInfo.setUpdateDate(LocalDateTime.now());
        zsInfo.setDelFlag(true);
        zsInfo.setUpdateBy(deleteBy);
        zsInfoMapper.updateById(zsInfo);

        enterpriseExamineeCertificateRelationService.lambdaUpdate()
                .eq(EnterpriseExamineeCertificateRelation::getZsInfoId, id)
                .remove();
    }

    /**
     * @Params:
     * @Description: 证书字段校验
     * @Author: STARF
     * @CreateDate: 2022/6/21 16:09
     **/
    @Override
    public Boolean checkZsNameExist(String zsId, String zsName, String officeId) {
        int count = zsInfoMapper.countZsByName(zsId, zsName, officeId);
        if (count > 0) {
            return Boolean.TRUE;
        }
        return Boolean.FALSE;

    }

    /**
     * @Params:
     * @Description: 证书信息
     * @Author: STARF
     * @CreateDate: 2022/6/21 16:40
     **/
    @Override
    public ZsInfoVO queryCertificate(String id) {
        ZsInfo zsInfo = zsInfoMapper.selectById(id);
        if (null == zsInfo) {
            return null;
        }

        return this.queryCertificateVO(zsInfo);
    }

    /**
     * @Params:
     * @Description: 补充证书信息
     * @Author: STARF
     * @CreateDate: 2022/6/23 15:13
     **/
    public ZsInfoVO queryCertificateVO(ZsInfo zsInfo) {
        ZsInfoVO vo = BeanUtil.toBean(zsInfo, ZsInfoVO.class);
        try {
            vo.setCertificateDataSourceDesc(CertificateDataSourceEnums.IMPORT.getCode().equals(zsInfo.getCertificateDataSource()) ? CertificateDataSourceEnums.IMPORT.getDesc() : CertificateDataSourceEnums.RELATE_CJ.getDesc());
            vo.setCertificateGrantObjectDesc(CertificateGrantObjectEnums.ARCHIVED.getCode().equals(zsInfo.getCertificateGrantObject()) ? CertificateGrantObjectEnums.ARCHIVED.getDesc() : CertificateGrantObjectEnums.EXAM_GRADE.getDesc());
            vo.setCertificateStatusDesc(CertificateStatusEnums.PUBLISHED.getCode().equals(zsInfo.getCertificateStatus()) ? CertificateStatusEnums.PUBLISHED.getDesc() : CertificateStatusEnums.UNPUBLISHED.getDesc());
            if (ObjectUtil.isNotEmpty(zsInfo.getCertificateValidityType())) {
                vo.setCertificateValidityTypeDesc(CertificateValidityType.getByCode(zsInfo.getCertificateValidityType()).getDesc());
            }

            //模板
            ZsTemplate zsTemplate = templateService.getById(zsInfo.getCertificateTemplateid());
            vo.setCertificateTemplateName(zsTemplate.getTemplateName());
            vo.setCertificateTemplateCode(zsTemplate.getTemplateCode());

            //设置删除限制
            if (CertificateStatusEnums.PUBLISHED.getCode().equals(zsInfo.getCertificateStatus())) {
                vo.setDeleteMark(0);
                vo.setDeleteLimit("证书发布状态为已发布的证书，不允许删除");
            }
            Integer all = iZsExamineeInfoService.countPublishNumByZsId(vo.getId(), null);
            if (null == all)
                all = Integer.valueOf(0);
            // 设置证书的应发已发人数
            Integer hasPublishNum = iZsExamineeInfoService.countPublishNumByZsId(vo.getId(), CertificateProvideStatusEnums.PROVIDED.getCode());
            if (null == hasPublishNum)
                hasPublishNum = Integer.valueOf(0);
            vo.setShouldPublishNum(all);
            vo.setHasPublishNum(hasPublishNum);
            vo.setUnPublishNum(all - hasPublishNum);
            // 计算百分比
            if (all.compareTo(0) > 0) {
                BigDecimal publishPercentage = new BigDecimal(hasPublishNum).divide(new BigDecimal(all), 2, BigDecimal.ROUND_HALF_UP);
                vo.setPublishPercentage(publishPercentage.multiply(new BigDecimal(100)));
            }
            Integer countSerialNumberIsNull = iZsExamineeInfoService.lambdaQuery()
                    .eq(BaseEntity::getDelFlag, Boolean.FALSE)
                    .eq(ZsExamineeInfo::getCertificateProvideStatus, CertificateProvideStatusEnums.UNPROVIDED.getCode())
                    .eq(ZsExamineeInfo::getZsId, zsInfo.getId())
                    .isNotNull(ZsExamineeInfo::getCertificateSerialNumber)
                    .count();
            vo.setCanPublish(countSerialNumberIsNull > CommonConstant.ZERO);
        } catch (Exception e) {
            log.error("# 查询证书详情封装DTO异常", e);
        }
        return vo;
    }

    /**
     * @Params:
     * @Description: 分页查询证书列表
     * @Author: STARF
     * @CreateDate: 2022/6/23 14:57
     **/
    @Override
    public IPage<ZsInfoVO> certificatePage(CertificatePageParam param) {
        IPage<ZsInfo> list = zsInfoMapper.queryPage(param);
        IPage<ZsInfoVO> page = list.convert(zsInfo -> {
            return this.queryCertificateVO(zsInfo);
        });

        return page;
    }

    /**
     * @Params:
     * @Description: 获取考试结果选项
     * @Author: STARF
     * @CreateDate: 2022/6/23 10:14
     **/
    @Override
    public List<ZsExamScoreLineVO> getExamScoreLine(String examId) {
        EnterpriseScoreLineListParam result = enterpriseExamScoreLineSettingService.getScoreLineVOList(examId, "0", null);
        if (CollectionUtils.isEmpty(result.getScoreLineData())) {
            return null;
        }
        EnterpriseScoreLineSaveParam enterpriseScoreLineSaveParam = result.getScoreLineData().get(0);
        List<ZsExamScoreLineVO> voList = new ArrayList<>();
        if (CommonEnums.SCORE_LINE_TYPE_0.getCode().equals(result.getType()) || CommonEnums.SCORE_LINE_TYPE_1.getCode().equals(result.getType())) {
            ZsExamScoreLineVO vo = new ZsExamScoreLineVO();
            vo.setId("N");
            vo.setRegionName(CommonEnums.QUALIFIED_STATUS_N.getDesc());
            vo.setRegionEndScore(enterpriseScoreLineSaveParam.getScoreLineItems().get(0).getScore());
            vo.setType(result.getType());
            vo.setComparisonType(enterpriseScoreLineSaveParam.getComparisonType());
            voList.add(vo);

            ZsExamScoreLineVO vo2 = new ZsExamScoreLineVO();
            vo2.setId("Y");
            vo2.setRegionStartScore(enterpriseScoreLineSaveParam.getScoreLineItems().get(0).getScore());
            vo2.setType(result.getType());
            vo2.setComparisonType(enterpriseScoreLineSaveParam.getComparisonType());
            vo2.setRegionName(CommonEnums.QUALIFIED_STATUS_Y.getDesc());
            voList.add(vo2);

        }

        if (CommonEnums.SCORE_LINE_TYPE_2.getCode().equals(result.getType())) {
            for (EnterpriseScoreLineSaveParam param : result.getScoreLineData()) {
                ZsExamScoreLineVO vo = new ZsExamScoreLineVO();
                vo.setId(param.getRegionName());
                vo.setRegionName(param.getRegionName());
                vo.setRegionStartScore(param.getRegionStartScore());
                vo.setRegionEndScore(param.getRegionEndScore());
                vo.setType(result.getType());
                vo.setComparisonType(param.getComparisonType());
                voList.add(vo);
            }
        }
        return voList;
    }

    /**
     * @Params: status：1-已发布，2-未发布
     * @Description: 证书(取消)发布
     * @Author: STARF
     * @CreateDate: 2022/6/24 11:45
     **/
    @Override
    public Boolean setCertificateStatus(String id, Integer status, String updateBy) {
        ZsInfo zsInfo = zsInfoMapper.selectById(id);
        zsInfo.setUpdateBy(updateBy);
        zsInfo.setUpdateDate(LocalDateTime.now());
        zsInfo.setCertificateStatus(status);
        int row = zsInfoMapper.updateById(zsInfo);
        if (row > 0) {
            return Boolean.TRUE;
        }
        return Boolean.FALSE;
    }

    /**
     * @Params:
     * @Description: 通过考试查询证书信息
     * @Author: STARF
     * @CreateDate: 2022/7/5 9:52
     **/
    @Override
    public ZsInfoVO examGetZsInfo(String examId) {
        QueryWrapper<ZsInfo> queryWrapper = new QueryWrapper<>();
        queryWrapper.lambda()
                .eq(ZsInfo::getExamId, examId)
                .eq(ZsInfo::getDelFlag, false)
                // 这里要升序排序是只拿取了一条数据的原因
                .orderByAsc(ZsInfo::getCertificateStatus)
                .last("limit 1");
        ZsInfo zsInfo = zsInfoMapper.selectOne(queryWrapper);
        if (null == zsInfo) {
            return null;
        }
        ZsInfoVO vo = BeanUtil.toBean(zsInfo, ZsInfoVO.class);
        return vo;
    }

    @Override
    public List<String> getExamsByOfficeId(String officeId, String zsId) {
        List<String> examids = zsInfoMapper.getExamIdsByOfficeId(officeId, zsId);
        return examids;
    }

    @Override
    public List<ZsInfo> listByExamId(String examId) {
        return this.lambdaQuery()
                .eq(BaseEntity::getDelFlag, Boolean.FALSE)
                .eq(ZsInfo::getExamId, examId)
                .eq(ZsInfo::getCertificatePublishType, CertificatePublishTypeEnums.RELATION_CJ_PUBLISH.getCode())
                .orderByDesc(BaseEntity::getCreateDate)
                .list();
    }

    @Override
    public ApiResponse<Object> checkZsData(ZsInfoDTO zsInfoDTO, Boolean isUpdate) {
        if (isUpdate) {
            try {
                CheckUtil.check(zsInfoDTO.getId(), "证书id", StrRuler.notEmpty());
            } catch (CheckException e) {
                return ApiResponse.builder().code(52402702).build();
            }
        }
        try {
            CheckUtil.check(zsInfoDTO.getCertificateName(), "证书名称", StrRuler.notEmpty());
        } catch (CheckException e) {
            return ApiResponse.builder().code(52402703).build();
        }
        Boolean nameExist = this.checkZsNameExist(zsInfoDTO.getId(), zsInfoDTO.getCertificateName(), zsInfoDTO.getOfficeId());
        if (nameExist) {
            return ApiResponse.builder().code(52402704).build();
        }
        try {
            CheckUtil.check(zsInfoDTO.getCertificateTemplateid(), "证书模板", StrRuler.notEmpty());
        } catch (CheckException e) {
            return ApiResponse.builder().code(52402705).build();
        }
        try {
            CheckUtil.check(zsInfoDTO.getCertificateDataSource(), "证书数据来源", IntRuler.notNull());
        } catch (CheckException e) {
            return ApiResponse.builder().code(52402706).build();
        }

        // 验证模板
        ZsTemplate zsTemplate = Optional.ofNullable(templateService.getById(zsInfoDTO.getCertificateTemplateid()))
                .orElseThrow(() -> new CustomException("无效的模板"));
        // 如果是关联成绩数据
        if (CertificateDataSourceEnums.RELATE_CJ.getCode().equals(zsInfoDTO.getCertificateDataSource())) {
            // 获取考试
            Optional.ofNullable(examInfoService.selectExamInfoByExamId(zsInfoDTO.getExamId()))
                    .map(ExamInfoVo::getExamNameConfigName)
                    .ifPresent(zsInfoDTO::setExamName);
            if (StringUtils.isEmpty(zsInfoDTO.getExamId()) || StringUtils.isEmpty(zsInfoDTO.getExamName())) {
                return ApiResponse.builder().code(52402607).build();
            }

            if (CertificateGrantObjectEnums.EXAM_GRADE.getCode().equals(zsInfoDTO.getCertificateGrantObject()) &&
                    StringUtils.isEmpty(zsInfoDTO.getExamScoreLines())) {
                return ApiResponse.builder().code(52402608).build();
            }

            // 验证证书专业
            List<SpecialityDTO> specialityDTOS = templateService.listSpecialities(zsTemplate.getId());
            String specialityName = specialityDTOS.stream()
                    .filter(data -> zsInfoDTO.getCertificateSubject().equals(data.getSpecialityValue()))
                    .findAny()
                    .map(SpecialityDTO::getSpecialityName)
                    .orElseThrow(() -> new CustomException("无效的证书专业"));
            zsInfoDTO.setCertificateSubjectDesc(specialityName);
            // 验证证书级别
            List<LevelDTO> levelDTOS = templateService.listLevels(zsInfoDTO.getCertificateSubject());
            String levelName = levelDTOS.stream()
                    .filter(data -> zsInfoDTO.getCertificateLevel().equals(data.getLevelValue()))
                    .findAny()
                    .map(LevelDTO::getLevelName)
                    .orElseThrow(() -> new CustomException("无效的证书级别"));
            zsInfoDTO.setCertificateLevelDesc(levelName);
        }

        // 证书发布设置为与成绩关联发布
        if (CertificatePublishTypeEnums.RELATION_CJ_PUBLISH.getCode().equals(zsInfoDTO.getCertificatePublishType())) {
            // 获取考试
            Optional.ofNullable(examInfoService.selectExamInfoByExamId(zsInfoDTO.getExamId()))
                    .map(ExamInfoVo::getExamNameConfigName)
                    .ifPresent(zsInfoDTO::setExamName);
            if (StrUtil.isBlank(zsInfoDTO.getExamId()) || StringUtils.isEmpty(zsInfoDTO.getExamName())) {
                return ApiResponse.builder().code(52402607).build();
            }
        }
        return null;
    }

    @SneakyThrows
    @Override
    public String batchDownloadCertificateNoSignature(String infoId, ExamResultDataDownLoadRecord finalProjectDataDownLoadRecord, HttpServletResponse response) {
        ZsInfo zsInfo = Optional.ofNullable(this.getById(infoId))
                .orElseThrow(() -> new CustomException("不存在的证书信息"));
        List<ZsExamineeInfo> examineesInfos = iZsExamineeInfoService.lambdaQuery()
                .eq(ZsExamineeInfo::getZsId, infoId)
                .eq(ZsExamineeInfo::getCertificateProvideStatus, CertificateProvideStatusEnums.PROVIDED.getCode())
                .eq(BaseEntity::getDelFlag, Boolean.FALSE)
                .list();
        log.debug("批量下载无电子签章证书生成数据：{}", FastJsonUtil.getBeanToJson(examineesInfos));

        String lastFileName = zsInfo.getCertificateName() + "_" + System.currentTimeMillis() + ".zip";
        Future<String> fileFuture = ExecutorPoolUtil.getPoolTaskExecutor().submit(() -> {
            String fileUrl = exportCertificateNoSignaturePdfZip(lastFileName, examineesInfos);
            // 更新文件下载记录
            if (StringUtils.isEmpty(fileUrl)) {
                // 更新文件下载记录
                finalProjectDataDownLoadRecord.setStatus(ExamResultDownLoadStatusEnum.fail.getStatus());
            } else {
                // 更新文件下载记录
                finalProjectDataDownLoadRecord.setStatus(ExamResultDownLoadStatusEnum.finish.getStatus());
            }
            finalProjectDataDownLoadRecord.setUpdateDate(LocalDateTime.now());
            finalProjectDataDownLoadRecord.setFileUrl(fileUrl);
            examResultDataDownLoadRecordService.updateById(finalProjectDataDownLoadRecord);
            return fileUrl;
        });
        String fileUrl = null;
        try {
            fileUrl = ExecutorPoolUtil.getPoolTaskExecutor().submit(() -> {
                while (!fileFuture.isDone()) {
                    ThreadUtil.sleep(100);
                }
                return fileFuture.get();
            }).get(globalConfig.getFileTimeOut(), TimeUnit.SECONDS);
        } catch (TimeoutException e) {
            log.error("# 22、{}文件生成超过指定等待时间，提前返回", lastFileName);
        }
        return fileUrl;
    }

    @Cacheable(value = RedisKeyConstant.SELECT_ZS_INFO_BY_ID, key = "#id")
    @Override
    public ZsInfo selectCacheById(String id) {
        return this.getById(id);
    }
}
